DAX - Discarding Null values

[Anonymous]

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,

COUNT = CALCULATE(DISTINCTCOUNT('TABLE'[patID]),FILTER(ALL('TABLE'),'TABLE'[Network] == EARLIER('TABLE'[Network]) && 'TABLE'[Status] =="Approved" ))

the output was,

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

[Ashish_Mathur]

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.

[tackytechtom · Accepted solution]

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/