topic Re: DAX syntax for SQL query in DAX Commands and Tips

DAX syntax for SQL query

deboec — Fri, 20 May 2022 10:00:08 GMT

Hi,

I have a SQL query for my database which I use for grouping entries in a survey table by specific departments.

As a result I want a simple table that looks somewhat like my SQL query.

SQL query

select md.DEPARTMENT, count(distinct mpqr.MEDIC_UID) from `20583`.MEDIC_PROPERTY_QUESTION_RESULT mpqr left join `20583`.MEDIC m on mpqr.MEDIC_UID = m.UID left join `20583`.MEDIC_DEPARTMENT md on m.DEPARTMENT_UID = md.UID group by md.DEPARTMENT order by 2 desc

Output:

How do I accomplish to get a table in Power BI?

In the end I only want to use a simple measure that gives me the [DEPARTMENT] for MAX([count(distinct mpqr.MEDIC_UID]) which means '525' in this specific case.

thank you

Re: DAX syntax for SQL query

daXtreme — Fri, 20 May 2022 15:13:06 GMT

It's not that easy or simple to translate a SQL query into DAX. One has to know the structure of the model first.

Re: DAX syntax for SQL query

Whitewater100 — Sat, 21 May 2022 13:03:06 GMT

Hi:

These answers depend on how your model is set up. I will paste a link to a file that shows the model set up and then thetwo measures for DISTINCTCOUNT.

I will link a file example for MAX Distinct Count. I have used my table names and you would substitute in your "Department" table name where I have Channel. and where I used Data[Order ID] that's where you put your table[column name] which I think is mpqr.[MEDIC_UID] but I can't tell. I hope this helps. It looks a little overwhelming but is not too hard if you go step by step. You'll notice the first DISTINCTCOUNT measure is referenced in the final measure (solution measure), MAX DISTINCT COUNT.

https://drive.google.com/file/d/10mpcXrtjdtE7BGiqhArgUafjTk7StYUQ/view?usp=sharing

Re: DAX syntax for SQL query

deboec — Mon, 23 May 2022 08:24:33 GMT

Thank you very much!

The measure helped me with my end goal:

Max Distinct CT = CALCULATE( VALUES( Channel[Channel], ), TOPN( 1, Channel[Channel], [DISTINCT COUNT Order ID], DESC ) )

In my case it was:

Max Distinct Values = CALCULATE( VALUES( MEDIC_DEPARTMENT[DEPARTMENT], ), TOPN( 1, MEDIC_DEPARTMENT[DEPARTMENT], [DISTINCT COUNT MEDIC_UID], DESC ) )