Reply
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count person who are joining and leaving
04-18-2024
08:54 PM
Hi. Seeking for your help regarding DAX.
I want to create a DAX that counts the people who join and leave the online event. I have created a visual that counts who joins in a specific hour. However, I wish to make a visual that adds the people to the count who join the online event and subtracts the number of people who leave in an hourly interval.
Below is the sample data
Name | start_time | end_time | duration (seconds) | Duration (Minutes) | Duration (Hour) |
PERSON_1131 | 12/03/2024 6:48:55 am | 12/03/2024 6:48:56 am | 1 | 0 | 0 |
PERSON_1282 | 12/03/2024 6:49:10 am | 12/03/2024 6:49:19 am | 9 | 0.2 | 0 |
PERSON_1320 | 12/03/2024 6:49:26 am | 12/03/2024 6:49:34 am | 8 | 0.1 | 0 |
PERSON_1326 | 12/03/2024 6:50:57 am | 12/03/2024 6:51:06 am | 49 | 0.8 | 0 |
PERSON_1107 | 12/03/2024 6:53:47 am | 12/03/2024 6:53:53 am | 6 | 0.1 | 0 |
PERSON_1076 | 12/03/2024 6:58:49 am | 12/03/2024 6:58:51 am | 2 | 0 | 0 |
PERSON_1004 | 12/03/2024 6:49:12 am | 12/03/2024 6:59:40 am | 1028 | 17.1 | 0.3 |
PERSON_1217 | 12/03/2024 7:00:39 am | 12/03/2024 7:00:44 am | 5 | 0.1 | 0 |
PERSON_1298 | 12/03/2024 7:00:24 am | 12/03/2024 7:00:44 am | 20 | 0.3 | 0 |
PERSON_1217 | 12/03/2024 7:00:56 am | 12/03/2024 7:00:59 am | 3 | 0 | 0 |
PERSON_1426 | 12/03/2024 6:50:25 am | 12/03/2024 7:01:00 am | 5075 | 84.6 | 1.4 |
PERSON_1326 | 12/03/2024 6:51:14 am | 12/03/2024 7:01:07 am | 4993 | 83.2 | 1.4 |
PERSON_1081 | 12/03/2024 7:01:20 am | 12/03/2024 7:01:23 am | 3 | 0 | 0 |
PERSON_1426 | 12/03/2024 7:01:21 am | 12/03/2024 7:01:24 am | 3 | 0 | 0 |
PERSON_1326 | 12/03/2024 7:01:12 am | 12/03/2024 7:01:57 am | 45 | 0.8 | 0 |
PERSON_1130 | 12/03/2024 7:02:05 am | 12/03/2024 7:02:08 am | 3 | 0 | 0 |
PERSON_1006 | 12/03/2024 7:02:13 am | 12/03/2024 7:02:14 am | 1 | 0 | 0 |
PERSON_1068 | 12/03/2024 7:00:54 am | 12/03/2024 7:02:18 am | 164 | 2.7 | 0 |
PERSON_1231 | 12/03/2024 7:02:32 am | 12/03/2024 7:02:35 am | 3 | 0 | 0 |
PERSON_1076 | 12/03/2024 7:02:42 am | 12/03/2024 7:02:44 am | 2 | 0 | 0 |
PERSON_1060 | 12/03/2024 7:02:50 am | 12/03/2024 7:02:51 am | 1 | 0 | 0 |
PERSON_1147 | 12/03/2024 7:02:19 am | 12/03/2024 7:02:55 am | 36 | 0.6 | 0 |
PERSON_1210 | 12/03/2024 7:03:19 am | 12/03/2024 7:03:20 am | 1 | 0 | 0 |
PERSON_1564 | 12/03/2024 7:02:51 am | 12/03/2024 7:03:26 am | 75 | 1.2 | 0 |
PERSON_1581 | 12/03/2024 7:01:39 am | 12/03/2024 7:03:36 am | 197 | 3.3 | 0.1 |
PERSON_1051 | 12/03/2024 7:03:34 am | 12/03/2024 7:03:48 am | 14 | 0.2 | 0 |
PERSON_1074 | 12/03/2024 7:04:29 am | 12/03/2024 7:04:31 am | 2 | 0 | 0 |
1 REPLY 1
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-06-2024
12:03 AM
Hi @third_hicana - create a calculated columns for the start and end hours in your main table.
Start Hour = HOUR([start_time])
End Hour = HOUR([end_time])
Create a separate table for the hours
HoursTable = DISTINCT(SELECTCOLUMNS('_Perso', "Hour", HOUR('_Perso'[start_time])))
create the measures to count people joining and leaving within each hour:
People Joining =
VAR SelectedHour = SELECTEDVALUE('HoursTable'[Hour])
RETURN
CALCULATE(
COUNTROWS('_Perso'),
FILTER('_Perso', HOUR('_Perso'[start_time]) = SelectedHour)
)
People Leaving =
VAR SelectedHour = SELECTEDVALUE('HoursTable'[Hour])
RETURN
CALCULATE(
COUNTROWS('_Perso'),
FILTER('_Perso', HOUR('_Perso'[end_time]) = SelectedHour)
)
Hope it works
Proud to be a Super User! | |

Helpful resources
Recommendations
Subject | Author | Posted | |
---|---|---|---|
02-13-2025 04:45 PM | |||
10-22-2024 04:13 AM | |||
09-02-2024 11:54 PM | |||
Anonymous
| 09-20-2016 06:27 AM | ||
12-11-2024 07:09 PM |
Top Kudoed Authors (Last Month)
User | Count |
---|---|
19 | |
17 | |
14 | |
13 | |
11 |