Power BI - DAX Concurrent Users Query

[frb_sega]

Hi Guys,

I'm looking for some help with a Dax query that I am struggling with.

I have a table containing login/logout audit information. It contains a username along with a login time and logout time.

I am looking to create a report that shows how many users were logged into the system minute by minute over time.

Can anyone assist with this?

Thanks for your help,

Felix

[v-huizhn-msft · Accepted solution]

Hi @frb_sega,

I try to reproduce your scenario. The sample data created list the users start-time and end time in the system, I calculate the count if users up to current time.

I create a calendar table using the formula below,

Calendar = CALENDAR(MIN(Table2[Month_Start_Date]),MAX(Table2[Month_End_Date]))

Create a measure to calculate the count of users.

count = CALCULATE(COUNTA(Table2[User]),FILTER(Table2,AND(Table2[Month_Start_Date]<=MAX('Calendar'[Date]),Table2[Month_End_Date]>MAX('Calendar'[Date]))))

Finally, create a table, select the CALENDAR[Date], and "count" measure as value level. Please see the following screenshot. Up to 2016/8/1, there are one users online. Until 2016/10/1, there are two users in the system.



If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[SanjayD2209]

Thank you so much for the solution. Could you also help me with how should I create a relationship between concurrent users, with the "Start Date and Time"?

I want to create Table 2 visual with Start time, end time, and username. This will be in addition to the Table 1 where I have the calendar date and count

So that, when select an entry from Table 1, it should highlight those entries in Table2 to highlight the concurrent users.

[fahad0211995]

Hi, Can you please help me? Can you please post the variable code you have used for logged-in users Minute by minute?
because the above-mentioned variable COUNT will only work for the day granularity

[v-huizhn-msft · Accepted solution]

Hi @frb_sega,

I try to reproduce your scenario. The sample data created list the users start-time and end time in the system, I calculate the count if users up to current time.

I create a calendar table using the formula below,

Calendar = CALENDAR(MIN(Table2[Month_Start_Date]),MAX(Table2[Month_End_Date]))

Create a measure to calculate the count of users.

count = CALCULATE(COUNTA(Table2[User]),FILTER(Table2,AND(Table2[Month_Start_Date]<=MAX('Calendar'[Date]),Table2[Month_End_Date]>MAX('Calendar'[Date]))))

Finally, create a table, select the CALENDAR[Date], and "count" measure as value level. Please see the following screenshot. Up to 2016/8/1, there are one users online. Until 2016/10/1, there are two users in the system.



If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[frb_sega]

Hey @v-huizhn-msft

This is brilliant, and i now have it working day by day, thanks 🙂

The issue I now have is that users log in and out multiple times per day, so i need the dates down to minute level rather than day level.

Is it possible to auto create a calendar table like you have done above minute by minute rather than day by day? 

Thanks again,

Felix

[frb_sega · Accepted solution]

FYI, if anytone was looking for further info, I have been given the below code on another forum and it works well:

Calendar = 
SELECTCOLUMNS (
CROSSJOIN (
CALENDAR (MIN(Sessions[startdate]), MAX(Sessions[enddate]) ),
DATATABLE (
"Hour", DATETIME,
{
{ "00:00" },
{ "01:00" },
{ "02:00" },
{ "03:00" },
{ "04:00" },
{ "05:00" },
{ "06:00" },
{ "07:00" },
{ "08:00" },
{ "09:00" },
{ "10:00" },
{ "11:00" },
{ "12:00" },
{ "13:00" },
{ "14:00" },
{ "15:00" },
{ "16:00" },
{ "17:00" },
{ "18:00" },
{ "19:00" },
{ "20:00" },
{ "21:00" },
{ "22:00" },
{ "23:00" }
}
)
),
"Date", [Date] + [Hour]
)

[sminonese]

I made a timetable with 10 minute increments. I will use this to calculate concurrently logged in users in 10 minute timeframes. Thanks to everyone in the previous posts for tips!

Time = 
VAR Increment = 10 // 10 minutes increment

VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0,23,1), "Hour", [Value])
VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 60-Increment, Increment), "Minute", [Value])
VAR LoginDates = CALENDAR(MIN(Sessions[startdate]),MAX(Sessions[enddate]))

RETURN

SELECTCOLUMNS(
CROSSJOIN(LoginDates, HourTable, MinuteTable),
"PeriodStart", [Date] + TIME([Hour],[Minute],0),
"PeriodEnd", [Date] + TIME([Hour],[Minute]+Increment,0)
)