Calculation to count only 1 for duplicate items

[ayush_mriti]

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: 

SM+KL total =

DISTINCTCOUNTNOBLANK('Employee Output final_Current'[KL_cnt]) + DISTINCTCOUNTNOBLANK('Employee Output final_Current'[Sm_cnt])

KL_cnt = IF(

    'Employee Output final_Current'[ATR_Role] =  "Kanban Lead(s)",

     'Employee Output final_Current'[EMPLID]

     )

Sm_cnt = IF(

       'Employee Output final_Current'[ATR_Role] =  "Scrum Master(s)",

     'Employee Output final_Current'[EMPLID]

     )

Thanks in Advance.

[Cookistador · Accepted solution]

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)"
)
)

[Ashish_Mathur · Accepted solution]

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)"))

[ayush_mriti · Accepted solution]

its giving me the count of 14 instead of 7.

[ayush_mriti]

The solution worked, I just have to make some adjustment to the calculation. Thank you all.

[Ashish_Mathur · Accepted solution]

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)"))

[ayush_mriti · Accepted solution]

its giving me the count of 14 instead of 7.

[Cookistador · Accepted solution]

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)"
)
)

[ayush_mriti · Accepted solution]

its giving me the count of 14 instead of 7.