Forum Discussion
DAX syntax for SQL query
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
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
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
)
)