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.
Hello all,
I need assistance in getting a peak of sessions for a specific period of time. I can already filter the sessions I want, down to a specific day but I need to know the peak of sessions that ran in parallel on that day, now.
I have a table "sessions" with fields "start_time" and "end_time". Both are date-time and recognized correctly by Power BI. I know I have to "max count" where all "end_times" are greater than all "start_times" for the sessions in this period.
I struggle to translate this into a Measure with count or countx function. I tried to search the web but did not find any hint on this condition based calculation.
Maybe someone can help me or already solved this problem.
Thank you in advance.
Best,
Marcel
@MarcelMaron,
In order for us to provide you appropriate DAX, could you please share dummy data of your table and post expected result based on the dummy data here?
Regards,
Lydia
Hello Lydia ( @v-yuezhe-msft ),
Thanks for getting in touch with me.
This would be something like:
ID Start_Time End_Time
122 2017-08-09 09:24:22.000 2017-08-09 10:23:22.000
123 2017-08-10 10:23:22.000 2017-08-10 11:23:22.000
124 2017-08-10 10:12:22.000 2017-08-10 11:28:22.000
125 2017-08-10 13:25:22.000 2017-08-10 14:25:22.000
126 2017-08-10 16:23:22.000 2017-08-10 16:35:22.000
127 2017-08-10 17:23:22.000 2017-08-10 18:23:22.000
128 2017-08-11 09:24:22.000 2017-08-11 10:23:22.000
yyyy-mm-dd hh:mm:ss:fff
So the sessions 123 and 124 were running in parallel on 2017-08-10 and the peak for that day would be 2.
Best,
Marcel
@MarcelMaron,
I am not clear about your logic. sessions 125,126,127 also runs on 2017-8-10, why not calculate them? And what is the peak for other days(2017-8-9,2017-8-11) would you like to get?
Regards,
Lydia
Hello Lydia ( @v-yuezhe-msft ),
The sessions 125,126,127 must be taken into consideration, too. I just wanted to point out they did not run in parallel and so the peak of parallel sessions for that day would be 2, still. If we would just look at the other days the peak of those days would be in this case 1. Or if we consider the whole period including all entries we have here it would still be 2.
Best,
Marcel
@MarcelMaron,
What logic do you use to determine if sessions run in parallel? When the sesstions start at same day and same hour and end at same day and same hour, they are in parallel? The result you want to get is as follows, right?
Date peak
2017-8-9 1
2017-8-10 2
2017-8-11 1
Regards,
Lydia
Hi Lydia @v-yuezhe-msft,
let's focus on one day as a period only. I am already able to filter the page for a specific day and apply a calculation.
The result you posted is right. But we would not devide into different days here but filter on a page or report basis for a specific period we want to see the number of concurrent sessions for. This could be month or day and does not matter.
A simple logic amount of concurrent sessions that ran in a certain period.
The SQL code would look something like this:
@start_date_period datetime, @end_date_period datetime, AS BEGIN WHEN (count(*)!=0) Then (max(concurrentNumber)+1) ELSE max(concurrentNumber) END as 'Peak concurrent sessions in the measured period' from ( Select start_time, ( SELECT count(*) FROM Sessions where start_time is not NULL and end_time is not NULL and start_time < checkQ.start_time and start_time > @start_date_period and end_time < @end_date_period and end_time > checkQ.start_time ) as concurrentNumber FROM Sessions as checkQ where start_time is not NULL and end_time is not NULL and start_time > @start_date_period and end_time < @end_date_period group by Start_Time ) as baseQuery END
Best,
Marcel
@MarcelMaron,
Could you please post the complete dummy data? And what DAX formula do you use currently?
Regards,
Lydia
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 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |