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,

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi,

Based on the description, you have solved the problem. If it's convenient, you can share the solution process, which can help others to better solve similar problems.

 

Best Regards,

Wisdom Wu