Convert SQL Query to DAX

[rmbernal]

Hi,

Does anyone know how I would convert this SQL case statement to DAX (see below)?   Any help would be very much appreciated

select distinct c.[crm_as_Contact] as contactid,
( select b.Otherval from testingAos_File b ,dim_person p where b.crm_as_Contact=p.ContactId and b.Level_of_Expertise ='Primary Expertise' and b.crm_as_Contact=c.crm_as_Contact ) as Prim_exp,
( select b.Otherval from testingAos_File b ,dim_person p where b.crm_as_Contact=p.ContactId and b.Level_of_Expertise ='Intermediate Expertise' and b.crm_as_Contact=c.crm_as_Contact ) as Interm_exp,
( select b.Otherval from testingAos_File b ,dim_person p where b.crm_as_Contact=p.ContactId and b.Level_of_Expertise ='Advanced Expertise' and b.crm_as_Contact=c.crm_as_Contact ) as Adv_exp
from testingAos_File c
where c.crm_as_Contact in (select contactid from Dim_Person)

Further details here:

I have 1 Power Bi report, using a data model, I have person table and this calculated table as source:

Calculated table:

I have more than one record by contact ID on the calculated table above.

Here example of my Person table:

Here I need: I'm expecting to see only 1 record by contact ID using level expertise as column for each one (Primary, Intermediate and Advanced) 

Translating my sql query to DAX for creating a new table, here I'm expecting to see on that new table:

Since relationship with Person table would be one to one, I could add columns of Prim_exp, interm_exp and Adv_exp only joining by contact ID what is my businness need.

I hope this help better to explain what I'm looking for. Any help of you would be gretly appretiated

Thanks!

[lbendlin]

Sounds like you can do this entirely in the UI, via the matrix visual. 

1:1  relationships are usually an indication that tables can be combined into one.

[rmbernal]

Hi @lbendlinm, I don't know how  to do that you are suggesting..matrix visual? I don't have yet the 1:1 relationship, that's I'm pretending converting the Sql query to a Dax and put the result into a Calculated table.

For now, only I have is this table:

This is not a 1:1 reationship, because I have 2 or more records by each Contact ID, I need only one record by each contact ID.  Could I do that just using matrix visual?  Could you please explain me further how to do that? I'm not super expert on Power Bi, so I'm trying to solve it, but any possible solution so far.

Further details about your solution with Matrix visual based in my screeshot examples above would be greatly appreciated.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[rmbernal]

@lbendlin please see my updated post abocve, I already added further information as you suggested. Thanks

[lbendlin]

- Forget SQL

- Describe your business problem

- Provide sample data

- indicate the expected outcome based on the sample data.