DAX: Distinct Count based on filtering & conditional

samsa18 0
Viewed 27

I'm trying to count the unique ids based on the 90 day amount. However, one problem I ran to is that it would count the 120 day amount, which is not what I want and highlighted in blue. The result should be 20 distinct values not 22. I'm still new to BI, so I'm not sure what syntax/formatting to use to tell my function to ignore those columns.

Here's the measure I used:

Unique Count Measure =
CALCULATE (
    DISTINCTCOUNT ( 'PSJH DQ 9-13-2022'[ID] ),
    FILTER ( 'PSJH DQ 9-13-2022', ( 'PSJH DQ 9-13-2022'[Day 90 Amount] ) )
)

screenshot of sample data

dax
1 Answers
Unique Count Measure =
CALCULATE (
    DISTINCTCOUNT ( 'PSJH DQ 9-13-2022'[ID] ),
    FILTER ( 'PSJH DQ 9-13-2022', 'PSJH DQ 9-13-2022'[Day 90 Amount] > 0 )
)
    - CALCULATE (
        DISTINCTCOUNT ( 'PSJH DQ 9-13-2022'[ID] ),
        FILTER ( 'PSJH DQ 9-13-2022', 'PSJH DQ 9-13-2022'[Day 120 Amount] > 0 )
    )
Umut K0
Related