Events in progress - Users logged in at anyone time - Query

[roscoegray]

Hi All,

New to Power Bi and struggling with a measure that i hope someone can help me with...

I have some user login and logout data, on separate tables and it repeat throughout the day, bit like this.

Date               Time      Action     User           Date               Time      Action     User   

01/01/2017    08:30     Login      1234            01/01/2017    09:00     Logoff     1234

01/01/2017    08:45     Login      1233            01/01/2017    09:30     Logoff     1233

01/01/2017    09:00     Login      1238            01/01/2017    10:00     Logoff     1238

01/01/2017    09:15     Login      1234            01/01/2017    10:10     Logoff     1234

02/01/2017    10:00     Login      1234            02/01/2017    13:00     Logoff     1234

03/01/2017    08:30     Login      1334            03/01/2017    10:00     Logoff     1334

04/01/2017    11:30     Login      1238            04/01/2017    14:00     Logoff     1238

What i am trying to do is produce a graph that shows me a count of the number of users logged in at any one time, i have date and time keys and can plot the logouts vs logins but i can figure out what the measure would be to do the count for online at any one time. For example, at 8.50 there are 2 users logged in.

Can anyone point me in the right direction? Many Thanks

[v-qiuyu-msft]

Hi @roscoegray,

You can create a table which stored the Date and Time value for a reference table. I wouldn't suggest you create a date time table with minute interval, as it will contains much many rows maybe cause performance issue. Take a sample to create a hourly table, means calculate how many users are on status at each hour. You can create a DateTime table below:

DateTime = SELECTCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN (
            CALENDAR ( "2017-01-01", "2017-01-04" ),
            SELECTCOLUMNS (
                ADDCOLUMNS ( CALENDAR ( "2016-01-01", "2016-01-24" ), "DAY", DAY ( [Date] ) ),
                "DAY", [DAY]
            )
        ),
        "dateTime", [Date]
            + IF ( [DAY] = 24, 0, [DAY] )
                / 24
    ),
    "Date", [Date],
    "DateTime", [dateTime]
)

Then create DateTime column via combine the Date and Time column in Login and Logoff tables:

DateTime = 'Login'[Date] & " " & 'Login'[Time]

Create a measure below:

CountPerHour = var m=MAX('DateTime'[DateTime])
return

CALCULATE(COUNTROWS('Login'),FILTER(ALL('Login'),'Login'[DateTime]<=m ))- CALCULATE(COUNTROWS('Logoff'),FILTER(ALL('Logoff'),'Logoff'[DateTime]<=m))

Best Regards,
QiuyunYu

[roscoegray]

Thank you @v-qiuyu-msft, your example was of great help!

I had to do it slightly differently in the end to get my desired outcome but i do have one more question... 

I have now combined the tables to give me one record per event (user logged in and logged out). 

I have my number of users online at anyone time and i have Bins of time (1min,15mins,30mins ect) and that works well for graphs, apart from i would really like to also show the minimum for any given time range. For example if the 1min Bin is showing a period with 1 user logged in, even in the 30min bin i would like to overlay that minimum value over the calculated value for that time period. (14:03 is my example)

This is one of the measures i am now using to give me users logged in (i play with the min/max and >< as it shows me a slightly different picture) Found from here classifying-and-solving-events...

S1 = Calculate (
CountRows ( events),
Filter (Values ( events[LoginDateTime] ), events[LoginDateTime] <= Min ( DateTime[DateTime] ) ),
Filter (Values ( events[LogoutDateTime] ), events[LogoutDateTime] >= Min ( DateTime[DateTime] ) )
)

and i get this for example...

And for the 30mins...

But i would like to plot the min for each 30min period, for example  14:00-14:30 would be 1...

Can you help again or can anyone else? 

Thank You

[v-qiuyu-msft]

Hi @roscoegray,

You can create a DateTime[DateTime] contains date time value with 30 minutes interval, eg: 8/1/2017 1:00:00, 8/1/2017 1:30:00, 8/1/2017 2:00:00, etc. then drag this column to the line chart X- axis.

Best Regards,
QiuyunYu

[eugee123]

May I know how to create a date time table with 30 mins or even 1 min interval? This is what i require to solve my PowerBI problem.

Thanks!