Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct count and grouping/filtering

I have the following table in my report:

 

PatientIDNumeratorVisitEndDate
11Jan 1, 2020
11

Feb 1, 2020

10Mar 1, 2020
21Jan 1, 2020
21

Feb 1, 2020

21Mar 1, 2020
31Jan 1, 2020
31

Feb 1, 2020

31Mar 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?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

     

    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.

  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Anonymous ,

     

    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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    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's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous ,

     

    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
  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey Anonymous ,

     

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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