Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Anonymous
Not applicable

Distinct Count with condition

Hi there

 

I have a replicated and summary table from the real dataset.

 

FloorUser NameSession TimeHourly
Level 1Alice8:38:008am
Level 1Alice9:06:009am
Level 4Elsa8:05:008am
Level 2Belle8:01:008am
Level 2Cinderella8:50:008am
Level 2Alice9:05:009am
Level 2Cinderella9:10:119am
Level 5Elsa8:15:008am
Level 3Belle8:12:008am
Level 3Alice9:04:009am
Level 6Elsa8:55:008am
Level 1Elsa9:00:009am
Level 4Freddie8:10:008am
Level 4Groot8:01:008am
Level 4Alice9:03:009am
Level 2Elsa9:00:019am
Level 4Freddie9:00:009am
Level 4Groot9:44:009am
Level 3Elsa9:00:029am
Level 5Hercules8:27:008am
Level 5Isabella8:43:008am
Level 5Jack8:52:008am
Level 5Alice9:02:009am
Level 5Duck9:33:009am
Level 4Elsa9:00:039am
Level 5Isabella9:01:009am
Level 5Jack9:02:009am
Level 5Elsa9:00:049am
Level 6Alice9:01:009am
Level 6Elsa9:00:059am

 

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) : 

vocc242_0-1618265838069.png

 

I have done some research on the matter but am completely stuck. really appreciate if any one can shed some light.

 

Thanks in advance

 

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , You can use dictinctcount(Table[User Name]) , Not clear on what issue your facing.

 

Can you share expected output ?

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.