Forum Discussion
Count Based on two columns
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
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())
4 Replies
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,
x_Applications Acted Upon = CALCULATE(DISTINCTCOUNT('Left Join'[LOANS.PAT_ID]), FILTER(ALL('Left Join'[Application Status]), 'Left Join'[Application Status] == "Acted"))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())