Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |