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

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

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors