Distinct Count with condition

Anonymous — Tue, 13 Apr 2021 04:22:49 GMT

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

Re: Distinct Count with condition

amitchandak — Tue, 13 Apr 2021 04:17:49 GMT

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

Can you share expected output ?

Re: Distinct Count with condition

Anonymous — Tue, 13 Apr 2021 10:45:07 GMT

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.

topic Re: Distinct Count with condition in DAX Commands and Tips

Distinct Count with condition

Re: Distinct Count with condition

Re: Distinct Count with condition