Forum Discussion
DAX - Discarding Null values
I have a following table,
| Network | patID | Status |
| A | 1 | Approved |
| A | 1 | Approved |
| A | Approved | |
| A | 1 | Approved |
| A | 2 | Declined |
| B | 3 | Approved |
| B | 4 | Approved |
| B | Approved | |
| B | 5 | Declined |
| B | 6 | Approved |
I wanted to write a calculated column called count which return distinct count of patID whose status is approved.
But it should not count the patID if its null,
I wrote the following DAX query,
| Network | patID | Status | count |
| A | 1 | Approved | 2 |
| A | 1 | Approved | 2 |
| A | Approved | 2 | |
| A | 1 | Approved | 2 |
| A | 2 | Declined | 2 |
| B | 3 | Approved | 4 |
| B | 4 | Approved | 4 |
| B | Approved | 4 | |
| B | 5 | Declined | 4 |
| B | 6 | Approved | 4 |
But the expected output was,
| Network | patID | Status | count |
| A | 1 | Approved | 1 |
| A | 1 | Approved | 1 |
| A | Approved | 1 | |
| A | 1 | Approved | 1 |
| A | 2 | Declined | 1 |
| B | 3 | Approved | 3 |
| B | 4 | Approved | 3 |
| B | Approved | 3 | |
| B | 5 | Declined | 3 |
| B | 6 | Approved | 3 |
Please help with the corrected query eliminating the count which invloves blank patID
Hi Anonymous ,
I think you almost had it!
I just added the <> BLANK() filter to your code:
COUNT = CALCULATE( DISTINCTCOUNT ('TABLE'[patID]), FILTER ( ALL('TABLE'), 'TABLE'[Network] = EARLIER('TABLE'[Network]) && 'TABLE'[Status] = "Approved" && 'TABLE'[patID] <> BLANK() ) )Does this solve your issue? 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
2 Replies
Hi Anonymous ,
I think you almost had it!
I just added the <> BLANK() filter to your code:
COUNT = CALCULATE( DISTINCTCOUNT ('TABLE'[patID]), FILTER ( ALL('TABLE'), 'TABLE'[Network] = EARLIER('TABLE'[Network]) && 'TABLE'[Status] = "Approved" && 'TABLE'[patID] <> BLANK() ) )Does this solve your issue? 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi,
This calculated column formula works
CALCULATE(DISTINCTCOUNT(Data[patID]),FILTER(Data,Data[Network]=EARLIER(Data[Network])&&Data[Status]="Approved"&&Data[patID]<>BLANK()))You do not need the ALL function.
