Need help with Occurrence of Distinct Count

[Sashwato]

Hello all,

I am trying to do a distinct count of people based on date ending period. So, if a person in Region X under Project A is counted once for a date period, then he/she need not be counted again for any other region and project as well. This is characterized by Date field available. Below screenshot is from the PowerQuery setup:

As you can see from above, I merged the Date and the username to generate a unique Index ID. I plotted a matrix chart to check and below is something I am getting:

If you take a look, on 1/31/2021 - Danny is distinct counted multiple times in projects: ABC, ZKY and GHI. We want Danny to be counted only once for that date period 1/31/2021. Currently I am doing Distinct count of the INDEX column. Please suggest any solution for the same.

Any help is appreciated.

Regards!

[v-stephen-msft · Accepted solution]

Hi @Sashwato ,

You can add an index column like

The Merged column is created by dax

Merged = [Date]&"-"&[Username]

Then I create the following rank column

Rank = RANKX(FILTER('Table',[Merged]=EARLIER('Table'[Merged])),[Index],,ASC,Dense)

Finally create a measure

Measure = IF(MAX('Table'[Rank])=1,1)

You can click Show items with no data to display empty values.

Check more details from the attachment

Best Regards,

Stephen Tao

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

[amitchandak]

@Sashwato , In the project view it will be counted or shown multiple times. But as you can see on grand total it counted once. That is correct.

[Sashwato]

Yes @amitchandak true. I agree, however our management would like to see the count against the name being shown once once rather than 3 times in other projects since they would like to track it on individual basis. 

Is there a measure or something that helps with it?

Regards!

[amitchandak]

@Sashwato , Try a measure like

measure =
var _1 = calculate(min(Table[project]), filter(allselected(Table) ,Table[project] = min(Table[project] ))
return
if( not(isblank(distinctCOUNT(Table[Index]))) && max(Table[project]) =_1 , distinctCOUNT(Table[Index]), blank())

[Sashwato]

Thank you @amitchandak 

I tried using the measure to my context as below:

Updated measure =

var _1 = calculate(min(Sheet1[Client]), filter(allselected(Sheet1) ,Sheet1[Client] = min(Sheet1[Client] )))

return

if( not(isblank(DISTINCTCOUNT(Sheet1[Index]))) && max(Sheet1[Client]) =_1 , DISTINCTCOUNT(Sheet1[Index]), blank())

I am seeing the below results still the same as it shows 1 on ABC, ZKY and GHI for Danny on 1/31/2021: