Forum Discussion
DISTINCT Count with Condition
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 ?
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.
5 Replies
- there are multiple ways and you can create a table with the code belowSumTable=ADDCOLUMNS(SUMMARIZE(TableName,TableName[Month],TableName[Centre]),"EmployeeCount",CALCULATE(DISTINCTCOUNT(TableName[Emoloyee No])))
- you can also create a measure with this codeEmployeeCount := DISTINCTCOUNT(TableName[Employee No])the plot a visual table with the Month, Centres and the measure.
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.
I've gotten this error message for this.. 😞
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
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.
