Forum Discussion

frb_sega's avatar
frb_sega
Frequent Visitor
9 years ago
Solved

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 log...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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's avatar
    frb_sega
    9 years ago

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

     

frb_sega's avatar
frb_sega
Frequent Visitor
9 years ago

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's avatar
sminonese
Frequent Visitor
6 years ago

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