Forum Discussion
How to apply multiple distinct counts
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
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
4 Replies
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970Hi 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
