Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
deboec
Helper I
Helper I

DAX syntax for SQL query

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:

image.png

 

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

1 ACCEPTED SOLUTION
Whitewater100
Solution Sage
Solution Sage

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 

Whitewater100_0-1653137897251.png

 

View solution in original post

3 REPLIES 3
Whitewater100
Solution Sage
Solution Sage

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 

Whitewater100_0-1653137897251.png

 

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
     )
)
daXtreme
Solution Sage
Solution Sage

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors