Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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
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
@Anonymous 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 <=.
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
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
Hi @ballist1x,
There is no need to create a relationship between DataTable1 and calendar table.
Regards,
Yuliana Gu
hi @v-yulgu-msft
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]
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |