cancel
Showing results for
Did you mean:
Helper III

PowerBI maximum Concurrent Callers - Stats DAX

Hi please can you help, i have data that has a call start date and time

Time_of_connection

Time_of_Disconnection

in Date/ Time format

I also have a User_ID field;

i need to calculate the number (Count) the maximum number of users on a call at any point in time (Daily)

How can i calculate this?

9 REPLIES 9
Microsoft

Hi @ballist1x,

Rajendran's suggestion worked on my test. But need some little modification:

```Count Measure =
CALCULATE (
COUNT ( Table1[User_ID] ),
FILTER (
Table1,
SELECTEDVALUE ( Date_Table[DateTime] ) >= Table1[Time_of_connection]
&& SELECTEDVALUE ( DateTime ) <= Table1[Time_of_Disconnection]
)
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

@v-yulgu-msft

thanks for that, what format should my date table be in for this to work?

and if i need to do a visualisation that showed the number of concurrent calls taking place on an hour by hour basis from 9am until 6pm each day how could i do this?

regards

Resident Rockstar

Iam assuming you have a date table and relationship is set properly. With that assumption, the below one should work, please try.

CALCULATE ( COUNT(Table1[User_ID]), FILTER( Table1, SELECTEDVALUE(Date_Table[DateTime]) >=Table1[Time_of_connection] && SELECTEDVALUE(DateTime) >=Table1[Time_of_Disconnection]) )

Thanks

Raj

Resident Rockstar

@rajendran wrote:

Iam assuming you have a date table and relationship is set properly. With that assumption, the below one should work, please try.

CALCULATE ( COUNT(Table1[User_ID]), FILTER( Table1, SELECTEDVALUE(Date_Table[DateTime]) >=Table1[Time_of_connection] && SELECTEDVALUE(DateTime) >=Table1[Time_of_Disconnection]) )

Thanks

Raj

Sorry for the spell mistake. The highlighted part should be <=.

Helper III

Hi my calendar table looks like this: i now need to do this by Minute/Hour

Calendar =
SELECTCOLUMNS (
CROSSJOIN (
CALENDAR (MIN(data[Time of Connection]), MAX(data[Time of Disconnection].[Date]) ),
DATATABLE (
"Hour", DATETIME,
{
{"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:24"},

and so on until 23:59
}
)
),
"Date", [Date] + [Minute]
)

what fields do i need to create the relationship between the Date and table? i currently have it set to Time_of_Connection and and the date table.

Microsoft

Hi @ballist1x,

Please format the data type of [Date] column in Calendar table to Date/Time, which is the same as data[Time of Connection] and data[Time of Disconnection].

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

they are all formatted as Date/Time

Format: (dd/MM/yy HH:mm)

should the relationship be between the Time_of_Connection and the calendar date data table?

Sample Data:

DataTable1

 Username Time_of_connection Time_of_disconnection JBEAN 14/7/2018 11:02 14/7/2018 11:05 BEEMER 14/7/2018 11:02 14/7/2018 11:06 HLEFT 14/7/2018 11:01 14/7/2018 11:08 JUP 14/7/2018 11:00 14/7/2018 11:05 JDOWN 14/7/2018 10:55 14/7/2018 10:58

So on a visualisation at the minute 14/7/2018:11:03 should show the number of users connected: 4

Microsoft

Hi @ballist1x,

There is no need to create a relationship between DataTable1 and calendar table.

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

hi @

without a relationship it doesnt calculate anything at all. with a relationship it doesnt calculater correctly:(

this is the atcual formula im using;

Count Measure =
CALCULATE (
FILTER (
Data,
SELECTEDVALUE ( 'Calendar'[Date] ) >= data[Time of Connection]
&& SELECTEDVALUE ('Calendar'[Date]) <= Data[Time of Disconnection]
)
)

Announcements