Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
frb_sega
Frequent Visitor

Power BI - DAX Concurrent Users Query

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

2 ACCEPTED SOLUTIONS
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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.


1.PNG

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.

2.PNG

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

Best Regards,
Angelia

 

View solution in original post

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]
)

 

View solution in original post

6 REPLIES 6
SanjayD2209
New Member

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
New Member

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
Microsoft Employee
Microsoft Employee

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.


1.PNG

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.

2.PNG

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

Best Regards,
Angelia

 

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

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]
)

 

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)
)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.