Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a Table,
N_Name | O_Name | O_Status | patID |
A | O1 | Completed | 8652 |
A | O1 | Completed | 8652 |
A | O1 | Completed | 8652 |
A | O1 | Completed | 8718 |
A | O1 | Completed | 8718 |
A | O1 | Completed | 8718 |
A | O2 | Completed | |
A | O2 | Completed | |
A | O2 | Completed | |
A | O3 | New | |
A | O4 | In Progress | |
A | O4 | In Progress | |
B | O5 | Completed | 8126 |
B | O5 | Completed | 8126 |
B | O5 | Completed | 8126 |
B | O5 | Completed | 8126 |
B | O5 | Completed | 8126 |
B | O5 | New |
I wanted produce a table visualization which looks like the below,
I wrote a dax query,
count = IF(ISNUMBER(DISTINCTCOUNT('Table'[PATID])),DISTINCTCOUNT('Table'[PATID]), 0)
But this produces a table like counting the null values of patID to be 1,
Please help with correct dax query to avoid the above null values.
Thanks
Solved! Go to Solution.
Hi,
This measure works
Count = coalesce(CALCULATE(DISTINCTCOUNT(Data[patID]),Data[patID]<>blank()),0)
Hope this helps.
Why have you used the ALL function there? Try this measure
x_Applications Acted Upon = CALCULATE(DISTINCTCOUNT('Left Join'[LOANS.PAT_ID]), 'Left Join'[Application Status] == "Acted",'Left Join'[LOANS.PAT_ID]<>BLANK())
Thank you so much Sir
Hi,
This measure works
Count = coalesce(CALCULATE(DISTINCTCOUNT(Data[patID]),Data[patID]<>blank()),0)
Hope this helps.
Sir, a quick question,
How do i implement the same (i.e) avoiding the null values
if my original dax query is something like the below,
Why have you used the ALL function there? Try this measure
x_Applications Acted Upon = CALCULATE(DISTINCTCOUNT('Left Join'[LOANS.PAT_ID]), 'Left Join'[Application Status] == "Acted",'Left Join'[LOANS.PAT_ID]<>BLANK())
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |