How to distinct count an ID only on the first date in DAX?

user_10010550 0
Viewed 50

I have a Table where an ID can occur over multiple dates due to having different states.

ID State DATE
A a 2022-01-01
A b 2022-01-02
A c 2022-01-03
B d 2022-01-01
B e 2022-01-02
C f 2022-01-03

I would like to create ONE measure to distinct count the IDs.
This measure is needed for KPI cards, Line Charts and table visuals.
This is easily done with:

Count = DISTINCTCOUNT('Table'[ID])

Displaying this measure in a table visual split by Date.

Date Count
2022-01-01 2
2022-01-02 2
2022-01-03 2
Total 3

A count will appear for each date where this ID occurred. The Total will always be correct.

However, I do not want to distinct count for each date. Just the first date at which the ID occurred.

Date Count
2022-01-01 2
2022-01-02 0
2022-01-03 1
Total 3
dax powerbi
2 Answers

First create a measured column :

Dates = calculate ( min('Table'[DATE]), 
               filter('Table','Table'[ID ] = EARLIER('Table'[ID ])))

then use your measure with the above column

Count = DISTINCTCOUNT('Table'[ID ])
user_52602650

This one will works fast. Just replace your measure with this one and get a result.

MyMeasure = 
VAR t =
    CALCULATETABLE( --new
        VALUES('table'[ID]) --new 
        ,FILTER(
            'table'
            ,VAR dateInRow=[DATE]
                RETURN  
                dateInRow=CALCULATE(
                                    min('table'[DATE])
                                    ,ALLEXCEPT('table','table'[ID]) 
                            )
        )
    ) -- new
VAR result = COUNTROWS(t)          
RETURN 
    IF(
      ISBLANK(result)
      ,0
      ,result
     )
user_190423790
Related