topic Distinctcount in a column based on the other column in DAX Commands and Tips

Distinctcount in a column based on the other column

jpaguiar79 — Thu, 09 Nov 2023 19:04:56 GMT

Hello community,

I have a table with these columns below and need to know how many unique employees per qty of hours.

Result should be: 3 employees with 189 hours (A, B, C) and 2 employees with 168 hours (D, E).

This is already a filtered table (by country). Field "hours" is a measure.

I tried Distinctcount with filter, countrows, etc.

THANKS!

jpaguiar79 — Thu, 09 Nov 2023 20:16:13 GMT

Solution:

VAR _ctmax = CALCULATE(DISTINCTCOUNT('table1'[employee]),FILTER('table1','table1'[hours] = 189))

VAR _ctmin = CALCULATE(DISTINCTCOUNT('table1'[employee]),FILTER('table1','table1'[hours] = 168))

And I put 189 and 168 into another VARs before these sentences to make sure I get max and min.

Thanks!