Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure - Get DistinctCount by Category

Hi all,

 

I'm finding it tricky to get the DistinctCount filtered or grouped by a Category.

 

For example, with the below sample data, the Distinctcount ("ID") for Monday is 3, Tuesday is 1, Wednesday is 2, Thursday is 4.

 

I want the measure to tell me the SUM of the above, so 3 + 1 + 2 + 4 = 10

 

IDDay of the Week
1Monday
1Monday
2Monday
3Monday
2Tuesday
2Tuesday
2Wednesday
3Wednesday
3Thursday
4Thursday
5Thursday
6Thursday

 

Thanks for your help! 

DAX
Measures
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Distinct count ID measure: =
SUMX (
    VALUES ( Data[Day of the Week] ),
    CALCULATE ( DISTINCTCOUNT ( Data[ID] ) )
)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks so much - that's what I was looking for!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Distinct count ID measure: =
SUMX (
    VALUES ( Data[Day of the Week] ),
    CALCULATE ( DISTINCTCOUNT ( Data[ID] ) )
)