Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
12 | |
12 | |
12 |