Count Based on two columns

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Count = coalesce(CALCULATE(DISTINCTCOUNT(Data[patID]),Data[patID]<>blank()),0)

Hope this helps.

[Anonymous]

Thank you so much Sir

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Count = coalesce(CALCULATE(DISTINCTCOUNT(Data[patID]),Data[patID]<>blank()),0)

Hope this helps.

[Anonymous]

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"))

[Ashish_Mathur · Accepted solution]

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())