Forum Discussion

roscoegray's avatar
roscoegray
Frequent Visitor
9 years ago

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

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 ...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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's avatar
roscoegray
Frequent Visitor
9 years ago

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's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    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's avatar
      eugee123
      New Member
      5 years ago

      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!