DISTINCT Count with Condition

[rinaemo]

Hi everyone, I have this table on PowerBI with 3 columns:

1. Month

2. Centres

3. Employee No

I need to do a distinct count on Employee No based on the month and centres but I am unable to do so 😞 can anyone advise ?

[v-yangliu-msft · Accepted solution]

Hi  @rinaemo ,

Based on your error message, my guess is that you copied and pasted the table creation DAX function from @FreemanZ  second reply into the new measure, and based on your latest requirements, I think you can achieve this by trying the new measure in @FreemanZ  third reply.

EmployeeCount = DISTINCTCOUNT(TableName[Employee No])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

there are multiple ways and you can create a table with the code below

SumTable=

ADDCOLUMNS(

    SUMMARIZE(

        TableName,

        TableName[Month],

        TableName[Centre]

     ),

   "EmployeeCount",

    CALCULATE(

  DISTINCTCOUNT(TableName[Emoloyee No])

    )

)

[rinaemo]

I've gotten this error message for this.. 😞

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[FreemanZ]

you can also create a measure with this code

EmployeeCount := DISTINCTCOUNT(TableName[Employee No])

the plot a visual table with the Month, Centres and the measure.

[rinaemo]

It does count the distinct employees, but when i plot in the centres it shows duplicate.

Because there's this specific month that this employee appeared twice in 2 different centre. So i am trying to get the unique value to eliminate the extra employee entry in another centre.