Forum Discussion
Calculation to count only 1 for duplicate items
Hi All,
I have this logic to count only 1 for duplicate EMPLID. Right now its counting all the duplicates and the total is not correct.
In the above table, EMPLID which are highlighted needs to be counted only 1 so that the total count is 7 instead of 9. Is there a solution to get this.
Here are the DAX calculations for SM+KL Totals:
Hello ayush_mriti
The following code should return what you need
SM+KL Distinct Total =
CALCULATE(
DISTINCTCOUNT('Employee Output final_Current'[EMPLID]),
FILTER(
'Employee Output final_Current',
'Employee Output final_Current'[ATR_Role] = "Kanban Lead(s)" ||
'Employee Output final_Current'[ATR_Role] = "Scrum Master(s)"
)
)Hi,
Does this work?
Measure = calculate(DISTINCTCOUNTNOBLANK('Employee Output final_Current'[KL_cnt]) + DISTINCTCOUNTNOBLANK('Employee Output final_Current'[Sm_cnt]), ('Employee Output final_Current'[ATR_Role]="Kanban Lead(s)"||'Employee Output final_Current'[ATR_Role]="Scrum Master(s)"))
its giving me the count of 14 instead of 7.
its giving me the count of 14 instead of 7.
5 Replies
Hello ayush_mriti
The following code should return what you need
SM+KL Distinct Total =
CALCULATE(
DISTINCTCOUNT('Employee Output final_Current'[EMPLID]),
FILTER(
'Employee Output final_Current',
'Employee Output final_Current'[ATR_Role] = "Kanban Lead(s)" ||
'Employee Output final_Current'[ATR_Role] = "Scrum Master(s)"
)
)its giving me the count of 14 instead of 7.
Hi,
Does this work?
Measure = calculate(DISTINCTCOUNTNOBLANK('Employee Output final_Current'[KL_cnt]) + DISTINCTCOUNTNOBLANK('Employee Output final_Current'[Sm_cnt]), ('Employee Output final_Current'[ATR_Role]="Kanban Lead(s)"||'Employee Output final_Current'[ATR_Role]="Scrum Master(s)"))
its giving me the count of 14 instead of 7.
The solution worked, I just have to make some adjustment to the calculation. Thank you all.