cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ballist1x Member
Member

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
Super User
Super User

Re: PowerBI maximum Concurrent Callers - Stats DAX

Hi @ballist1x

 

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

Community Support Team
Community Support Team

Re: PowerBI maximum Concurrent Callers - Stats DAX

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.
ballist1x Member
Member

Re: PowerBI maximum Concurrent Callers - Stats DAX

@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

Super User
Super User

Re: PowerBI maximum Concurrent Callers - Stats DAX


@rajendran wrote:

Hi @ballist1x

 

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 <=.

ballist1x Member
Member

Re: PowerBI maximum Concurrent Callers - Stats DAX

@rajendran

 

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.

Community Support Team
Community Support Team

Re: PowerBI maximum Concurrent Callers - Stats DAX

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.
ballist1x Member
Member

Re: PowerBI maximum Concurrent Callers - Stats DAX

Hi @v-yulgu-msft

 

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

 

UsernameTime_of_connectionTime_of_disconnection
JBEAN14/7/2018 11:0214/7/2018 11:05
BEEMER14/7/2018 11:0214/7/2018 11:06
HLEFT14/7/2018 11:0114/7/2018 11:08
JUP14/7/2018 11:00

14/7/2018 11:05

JDOWN14/7/2018 10:5514/7/2018 10:58

 

 

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

Community Support Team
Community Support Team

Re: PowerBI maximum Concurrent Callers - Stats DAX

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.
ballist1x Member
Member

Re: PowerBI maximum Concurrent Callers - Stats DAX

hi @ 

 

without a relationship it doesnt calculate anything at all. with a relationship it doesnt calculater correctlySmiley Sad

 

this is the atcual formula im using;

 

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