Distinct count and grouping/filtering

[bireportsHG]

I have the following table in my report:

PatientID	Numerator	VisitEndDate
1	1	Jan 1, 2020
1	1	Feb 1, 2020
1	0	Mar 1, 2020
2	1	Jan 1, 2020
2	1	Feb 1, 2020
2	1	Mar 1, 2020
3	1	Jan 1, 2020
3	1	Feb 1, 2020
3	1	Mar 1, 2020

I would like to include a measure that brings back the distinct count of patients that have a minimum numerator of 1. In this scenario, the count should be 2 (patientid 2 & 3). How would the expression be written to return this count?

[V-pazhen-msft · Accepted solution]

@bireportsHG 

Try something like this:

Measure = 
var Table_unmatch=CALCULATETABLE(VALUES('Table'[PatientID]),FILTER('Table','Table'[Numerator]=0))
Return CALCULATE(DISTINCTCOUNT('Table'[PatientID]),FILTER('Table',NOT([PatientID] in Table_unmatch)))

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33]

You can groupby PatientID, Calculate Min, then filter the table to the Min = 1, then count rows:

Count=
VAR T1 = GROUPBY(Table,[PatientID],"@MIN",MINX(CURRENTGROUP(),[Numerator]))

RETURN

COUNTROWS(FILTER(T1,[@MIN]=1))

[TomMartens · Accepted solution]

Hey @bireportsHG ,

here is another DAX statement that is very efficient and compact:

tom = 
SUMX(
    VALUES('Table'[PatientID])
    , var _min = CALCULATE(MIN('Table'[Numerator]))
    return
    IF(_min = 1 , 1 , BLANK())
)

It iterates across the distinct PatientID that are available in the current filter context
SUMX(
    VALUES(,,,
determines the minimum Numerator for each PatientID
CALCULATE(MIN(...
checks if the value matches the condition and returns 1 otherwise BLANK()
Finally, all the 1's will be summed by the iterator function SUMX

Hopefully, this provides what you are looking for.

Regards,
Tom

[V-pazhen-msft · Accepted solution]

@bireportsHG 

Try something like this:

Measure = 
var Table_unmatch=CALCULATETABLE(VALUES('Table'[PatientID]),FILTER('Table','Table'[Numerator]=0))
Return CALCULATE(DISTINCTCOUNT('Table'[PatientID]),FILTER('Table',NOT([PatientID] in Table_unmatch)))

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @bireportsHG ,

Try this code:

Measure =

VAR _tbl_0 = SELECTCOLUMNS(CALCULATETABLE('Table'; FILTER(ALL('Table'[Numerator]); 'Table'[Numerator] = 0)); "PatientID"; 'Table'[PatientID])

VAR _tbl_1 = SELECTCOLUMNS(CALCULATETABLE('Table'; FILTER(ALL('Table'[Numerator]); 'Table'[Numerator] >=1)); "PatientID"; 'Table'[PatientID])

VAR _result = EXCEPT(_tbl_1;_tbl_0)

RETURN AVERAGEX(_result; 1)

Ricardo