cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ballist1x
Helper III
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
v-yulgu-msft
Microsoft
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.

@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

rajendran
Resident Rockstar
Resident Rockstar

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


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

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

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.

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

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.

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 (
    COUNTA(data[callingPartyUnicodeLoginUserID] ),
    FILTER (
        Data,
        SELECTEDVALUE ( 'Calendar'[Date] ) >= data[Time of Connection]
            && SELECTEDVALUE ('Calendar'[Date]) <= Data[Time of Disconnection]
    )
)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.