Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi there
I have a replicated and summary table from the real dataset.
Floor | User Name | Session Time | Hourly |
Level 1 | Alice | 8:38:00 | 8am |
Level 1 | Alice | 9:06:00 | 9am |
Level 4 | Elsa | 8:05:00 | 8am |
Level 2 | Belle | 8:01:00 | 8am |
Level 2 | Cinderella | 8:50:00 | 8am |
Level 2 | Alice | 9:05:00 | 9am |
Level 2 | Cinderella | 9:10:11 | 9am |
Level 5 | Elsa | 8:15:00 | 8am |
Level 3 | Belle | 8:12:00 | 8am |
Level 3 | Alice | 9:04:00 | 9am |
Level 6 | Elsa | 8:55:00 | 8am |
Level 1 | Elsa | 9:00:00 | 9am |
Level 4 | Freddie | 8:10:00 | 8am |
Level 4 | Groot | 8:01:00 | 8am |
Level 4 | Alice | 9:03:00 | 9am |
Level 2 | Elsa | 9:00:01 | 9am |
Level 4 | Freddie | 9:00:00 | 9am |
Level 4 | Groot | 9:44:00 | 9am |
Level 3 | Elsa | 9:00:02 | 9am |
Level 5 | Hercules | 8:27:00 | 8am |
Level 5 | Isabella | 8:43:00 | 8am |
Level 5 | Jack | 8:52:00 | 8am |
Level 5 | Alice | 9:02:00 | 9am |
Level 5 | Duck | 9:33:00 | 9am |
Level 4 | Elsa | 9:00:03 | 9am |
Level 5 | Isabella | 9:01:00 | 9am |
Level 5 | Jack | 9:02:00 | 9am |
Level 5 | Elsa | 9:00:04 | 9am |
Level 6 | Alice | 9:01:00 | 9am |
Level 6 | Elsa | 9:00:05 | 9am |
I would like to perform distinct count by user name per floor per hourly with condition that if a user visited multiple floors with the hour than only count once with the earliest session time.
eg. Elsa visited level 4, 5 & 6 within 8am (hourly column) and the earliest session time of all were the 8:05:00 which she visited level 4. so count her once for level 4 at 8am and ignore the rest of her visited floors within the hour.
Also, Elsa visited every floor within 9am but count her once for level 1 at 9am (earliest session time).
Expected output as below (sometimes its easier in excel) :
I have done some research on the matter but am completely stuck. really appreciate if any one can shed some light.
Thanks in advance
Thanks for your suggestion.
Although, if a distinct count is used, the sum of the total for all floors would be 30. I would like to distinct count by the first visit to a floor within an hourly time frame.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
8 |