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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vocc242
Frequent Visitor

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

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

 

Can you share expected output ?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors