Forum Discussion

coolioaus's avatar
coolioaus
New Member
1 year ago
Solved

Creating concurrent usage data

Hi All,   I'm new to Power BI and trying to get my head around something which is probably relatively simple. We have a system that tracks users signing in and out. This may be once per day or it m...
Need Help
Show and Tell
Tips and Tricks
  • Thejeswar's avatar
    Thejeswar
    1 year ago

    Hi coolioaus ,

    The Solution I would use will go like this

    The Below is the dataset I used for this purpose

    Username Log In Time Log Out Time

    user12024-06-11 23:37:352024-06-12 00:00:35
    user12024-06-11 05:44:122024-06-11 05:54:12
    user12024-06-11 02:43:112024-06-11 03:16:11
    user22024-06-11 03:08:042024-06-11 03:55:04
    user22024-06-11 11:11:152024-06-11 11:41:15
    user22024-06-11 09:10:312024-06-11 10:56:31
    user32024-06-11 17:49:072024-06-11 18:35:07
    user32024-06-11 11:07:012024-06-11 12:00:01
    user32024-06-11 20:55:092024-06-11 22:32:09
    user42024-06-11 05:12:142024-06-11 06:54:14
    user42024-06-11 00:32:342024-06-11 02:25:34
    user42024-06-11 12:35:522024-06-11 14:17:52
    user52024-06-11 19:36:172024-06-11 20:57:17
    user52024-06-11 07:51:392024-06-11 08:14:39
    user52024-06-11 04:09:192024-06-11 05:40:19

    1. Create a TimeTable as below

     

     

    TimeTable = 
    GENERATESERIES(
        TIME(0,0,0), 
        TIME(23,59,0), 
        TIME(0,15,0)   
        )

     

     

    2. Loaded the data into Power Query and extracted time as separate columns

    You can do this using the Time Dropdown in Power Query Editor

    3. Created the below DAX

     

     

    ConcurrentSignIns = 
VAR CurrentTime = SELECTEDVALUE(TimeTable[Time])
RETURN
COUNTROWS(
FILTER(
'Table',
'Table'[LogInTime] <= CurrentTime &&
'Table'[LogOutTime] >= CurrentTime
)
)

     

     

    4. You can build a Line Chart, with Time from TimeTable in your X-axis and the ConcurrentSignIns measure on the Y-axis

     

    Regards,

Thejeswar's avatar
Thejeswar
Super User
1 year ago

Hi coolioaus ,

The Solution I would use will go like this

The Below is the dataset I used for this purpose

Username Log In Time Log Out Time

user12024-06-11 23:37:352024-06-12 00:00:35
user12024-06-11 05:44:122024-06-11 05:54:12
user12024-06-11 02:43:112024-06-11 03:16:11
user22024-06-11 03:08:042024-06-11 03:55:04
user22024-06-11 11:11:152024-06-11 11:41:15
user22024-06-11 09:10:312024-06-11 10:56:31
user32024-06-11 17:49:072024-06-11 18:35:07
user32024-06-11 11:07:012024-06-11 12:00:01
user32024-06-11 20:55:092024-06-11 22:32:09
user42024-06-11 05:12:142024-06-11 06:54:14
user42024-06-11 00:32:342024-06-11 02:25:34
user42024-06-11 12:35:522024-06-11 14:17:52
user52024-06-11 19:36:172024-06-11 20:57:17
user52024-06-11 07:51:392024-06-11 08:14:39
user52024-06-11 04:09:192024-06-11 05:40:19

1. Create a TimeTable as below

 

 

TimeTable = 
    GENERATESERIES(
        TIME(0,0,0), 
        TIME(23,59,0), 
        TIME(0,15,0)   
        )

 

 

2. Loaded the data into Power Query and extracted time as separate columns

You can do this using the Time Dropdown in Power Query Editor

3. Created the below DAX

 

 

ConcurrentSignIns = 
VAR CurrentTime = SELECTEDVALUE(TimeTable[Time])
RETURN
COUNTROWS(
FILTER(
'Table',
'Table'[LogInTime] <= CurrentTime &&
'Table'[LogOutTime] >= CurrentTime
)
)

 

 

4. You can build a Line Chart, with Time from TimeTable in your X-axis and the ConcurrentSignIns measure on the Y-axis

 

Regards,