Multiple conditions on same column power bi

[Anonymous]

So, I have a table called certifications.

Certificaitons has the columns: ID, course_id, certification_user_id.

I am trying to get the number of users who have completed both course 1 and course 2.
This can be accomplished in sql by joins and group by - having count.

I picture the sql query to be something like this:

select distinct (certification_user_id) from certifications c1 join certifications c2 on c1.certification_user_id = c2.certification_user_id

where c1.course_id = 1 and c2.course_id = 2;

Can someone point me in the right direction as to how to accomplish the same in Power BI? 

[Anonymous · Accepted solution]

If you have the ability to write an SQL statement that grabs the data for you, you can directly use that SQL statement in Power BI.  When you "Get Data", select SQL Server as your data connector.  You will get a screen that asks for the Server Name and Database.  Down below is an area "Advanced Options", which lets you place in SQL statements.

If your question was more about having the data already and replicating how you might go about solving this issue:

Within Power Query you can take your Certifications table and "reference" it.  This creates a new query/table that contains the data of when the certifications is finshed importing.  Cut this table down to simply be a concise list of User Details (Keep only required columns, remove duplicates).

From here you could do a join back to the Certification table and bring in the ID column.  You will now have 1 row per ID for that certification.  From here you can Pivot the data, giving you 1 column per ID.  Now you could create a column that does a count of each of those ID columns with data.

There might be other more efficient ways of doing this also, this was just off the top of my head.

[Anonymous]

@Anonymous

It is about the latter. I have the data ready.. I will create a reference and do a join per your suggestion. thanks a lot for your help,