How to apply multiple distinct counts

[Anonymous]

I have a table that i need to count distinct values from. See simplified table example below (latest hour for day is a calculated column)

UserID	Date	Hour	Latest Hour for day
John Smith	10/01/2020	10	10
John Smith	10/01/2020	10	10
John Smith	11/01/2020	9	9
Sam Benson	10/01/2020	10	10
Sam Benson	11/01/2020	10	12
Sam Benson	11/01/2020	12	12

I need to have multiple measures (for each hour of the working day - roughly 12 hours). For example i need the measure for hour 10 to tell me how many times a users latest hour for the day = 10. But this can only count the the same user once per day and it needs to count every single day in the range. In the table example above for hour 10 it should return 2 because on 10/01/2020 both John Smith and Sam Bensons latest hour was 10. Sometimes one user will may multiple rows for the same date and hour which complicates things.

Hope this makes sense,

Thanks

[amitchandak · Accepted solution]

did not get completely is with example

but

calculate(distinctcount(UserID),filter(table, [hour]= [latest hour of day] && [hour] =10))

The date and hour can filter from slicer. Also you can have flag created as column where hour = lastest hour. Using ealier you can flag each date.

Can provide more details and  explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

Thanks for this. I think you're on the right lines. I think it counts all the rows i need but due to the distinct count it only counts each userID once when it needs to count it once per day.

[amitchandak]

Please provide output example.

[v-juanli-msft]

Hi @Anonymous 

What does this mean?

I think it counts all the rows i need but due to the distinct count it only counts each userID once when it needs to count it once per day

Could you show some examples?

Best Regards
Maggie