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

Question

Hi All,&nbsp;New to Power Bi and struggling with a measure that i hope someone can help me with...&nbsp;I have some user login and logout data, on separate tables and it repeat throughout the day, bit like this.&nbsp;Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time &nbsp; &nbsp; &nbsp;Action &nbsp; &nbsp; User &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time &nbsp; &nbsp; &nbsp;Action &nbsp; &nbsp; User &nbsp;&nbsp;01/01/2017 &nbsp; &nbsp;08:30 &nbsp; &nbsp; Login&nbsp; &nbsp; &nbsp; 1234 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2017 &nbsp; &nbsp;09:00 &nbsp; &nbsp; Logoff &nbsp; &nbsp; 123401/01/2017 &nbsp; &nbsp;08:45 &nbsp; &nbsp; Login&nbsp; &nbsp; &nbsp; 1233 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2017 &nbsp; &nbsp;09:30 &nbsp; &nbsp; Logoff &nbsp; &nbsp; 123301/01/2017 &nbsp; &nbsp;09:00 &nbsp; &nbsp; Login&nbsp; &nbsp; &nbsp; 1238 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2017 &nbsp; &nbsp;10:00 &nbsp; &nbsp; Logoff &nbsp; &nbsp; 123801/01/2017 &nbsp; &nbsp;09:15 &nbsp; &nbsp; Login&nbsp; &nbsp; &nbsp; 1234 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01/01/2017 &nbsp; &nbsp;10:10 &nbsp; &nbsp; Logoff &nbsp; &nbsp; 123402/01/2017 &nbsp; &nbsp;10:00 &nbsp; &nbsp; Login&nbsp; &nbsp; &nbsp; 1234 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2017 &nbsp; &nbsp;13:00 &nbsp; &nbsp; Logoff &nbsp; &nbsp; 123403/01/2017 &nbsp; &nbsp;08:30 &nbsp; &nbsp; Login&nbsp; &nbsp; &nbsp; 1334 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;03/01/2017 &nbsp; &nbsp;10:00 &nbsp; &nbsp; Logoff &nbsp; &nbsp; 133404/01/2017 &nbsp; &nbsp;11:30 &nbsp; &nbsp; Login&nbsp; &nbsp; &nbsp; 1238 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04/01/2017 &nbsp; &nbsp;14:00 &nbsp; &nbsp; Logoff &nbsp; &nbsp; 1238&nbsp;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&nbsp;example,&nbsp;at 8.50 there are 2 users logged in.&nbsp;Can anyone point me in the right direction? Many Thanks&nbsp;

v-qiuyu-msft · Answer

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

v-qiuyu-msft · Answer

Hi roscoegray,
&nbsp;
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&nbsp;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:
&nbsp;
DateTime = SELECTCOLUMNS (&nbsp;&nbsp;&nbsp; ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSSJOIN (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALENDAR ( "2017-01-01", "2017-01-04" ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECTCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ADDCOLUMNS ( CALENDAR ( "2016-01-01", "2016-01-24" ), "DAY", DAY ( [Date] ) ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "DAY", [DAY]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "dateTime", [Date]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + IF ( [DAY] = 24, 0, [DAY] )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; / 24&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp; "Date", [Date],&nbsp;&nbsp;&nbsp; "DateTime", [dateTime])
&nbsp;
Then create DateTime column via combine the Date and Time column in Login and Logoff tables:
&nbsp;
DateTime = 'Login'[Date] &amp; " " &amp; 'Login'[Time]
&nbsp;
Create a measure below:
&nbsp;
CountPerHour = var m=MAX('DateTime'[DateTime])return
CALCULATE(COUNTROWS('Login'),FILTER(ALL('Login'),'Login'[DateTime]&lt;=m ))- CALCULATE(COUNTROWS('Logoff'),FILTER(ALL('Logoff'),'Logoff'[DateTime]&lt;=m))
&nbsp;

&nbsp;
Best Regards,QiuyunYu
&nbsp;

roscoegray · Answer

Thank you&nbsp;v-qiuyu-msft, your example&nbsp;was of great help!&nbsp;I had to do it slightly differently&nbsp;in the end to get my desired&nbsp;outcome but i do have one more question...&nbsp;&nbsp;I have now combined the tables to give me one record per event (user logged in and logged out).&nbsp;&nbsp;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&nbsp;if the 1min&nbsp;Bin is showing a period with 1 user logged in, even in the 30min bin i would like to overlay that minimum value&nbsp;over the calculated value&nbsp;for that time period. (14:03 is my example)&nbsp;This is one of the measures i am now using to give me users logged in (i play with the min/max and &gt;&lt; as it shows me a slightly&nbsp;different&nbsp;picture) Found from here&nbsp;classifying-and-solving-events...&nbsp;S1 = Calculate (
CountRows ( events),
Filter (Values ( events[LoginDateTime] ), events[LoginDateTime] &lt;= Min ( DateTime[DateTime] ) ),
Filter (Values ( events[LogoutDateTime] ), events[LogoutDateTime] &gt;= Min ( DateTime[DateTime] ) )
)&nbsp;and i get this for example...&nbsp;And for the 30mins...&nbsp;&nbsp;&nbsp;&nbsp;But i would like to plot the min for each 30min period, for example&nbsp; 14:00-14:30 would be 1...&nbsp;Can you help again or can anyone else?&nbsp;&nbsp;Thank You&nbsp;&nbsp;

eugee123 · Answer

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.&nbsp;Thanks!

Forum Discussion

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

4 Replies

Related content

Events in Progress Problem

Data Activator Events in the Audit Logs

Monitor Fabric Workspace activity - Item job event logs

Events in progress with datediff

Help With Event In Progress Visualization