How to get extract active event given the time interval dynamically?

[saadnaveed]

I have a table that looks something like this:

+----------+-------------------------------+-------------------------------+
| E_ID         | StartTime                               | EndTime                                |
+----------+-------------------------------+-------------------------------+
| 18 | 2013-04-10 16:33:11.735342100 | 2013-04-11 17:16:47.976164100 |
| 23 | 2013-04-11 16:33:46.575337300 | 2013-04-11 18:10:08.428443900 |
| 19 | 2013-04-11 17:17:04.033083300 | 2013-04-11 18:10:13.907757900 |
| 16 | 2013-04-11 18:10:24.293352100 | 2013-04-11 18:45:17.754240800 |
| 24 | 2013-04-11 18:11:20.278558900 | 2013-04-11 18:45:00.435247300 |
| 17 | 2013-04-15 19:42:22.549026700 | 2013-04-15 23:54:33.389964300 |
| 30 | 2013-04-16 17:42:24.588807700 | 2013-04-16 17:42:41.077751300 |
| 14 | 2013-04-19 16:51:22.699240800 | 2013-04-19 18:39:03.167468100 |
| 31 | 2013-04-19 18:30:56.891621300 | 2013-04-19 19:42:50.418640200 |
| 10 | 2013-04-20 16:07:07.327879000 | 2013-04-20 22:17:17.783053600 |
| 25 | 2013-04-22 18:40:03.193588300 | 2013-04-22 18:45:39.692862500 |
| 35 | 2013-04-22 22:37:44.611643600 | 2013-04-23 22:58:59.614665600 |
| 36 | 2013-04-27 10:35:15.924381800 | 2013-04-27 17:06:34.757135400 |
| 38 | 2013-04-01 10:56:37.887895200 | 2013-04-01 18:48:45.523163200 |
| 03 | 2013-04-05 09:50:59.676090700 | 2013-04-05 23:10:59.997149500 |
| 33 | 2013-04-08 20:04:35.081422100 | 2013-04-09 22:42:06.391661100 |
| 29 | 2013-04-13 17:04:50.256379600 | 2013-04-13 23:02:24.006316400 |
| 34 | 2013-04-14 05:37:12.330001300 | 2013-04-14 18:32:15.545483100 |
| 04 | 2013-04-15 03:28:57.726491800 | 2013-04-15 23:33:33.332273600 |
| 44 | 2013-04-17 01:42:28.476431100 | 2013-04-17 21:44:21.853923500 |
+----------+-------------------------------+-------------------------------+

I need to find events that are active during certain time period e.g. 12-14, but I want to do this dynamically, using a range slider. But there are some issues as from above data you can see that some events starts from previous date and were active during my desired interval. How to compare those.

My desired output looks like this:

+----------+-------------------------------+-------------------------------+
| E_ID         | StartTime                               | EndTime                                 |
+----------+-------------------------------+-------------------------------+
| 18 | 2013-04-10 16:33:11.735342100 | 2013-04-11 17:16:47.976164100 |
| 35 | 2013-04-22 22:37:44.611643600 | 2013-04-23 22:58:59.614665600 |
| 36 | 2013-04-27 10:35:15.924381800 | 2013-04-27 17:06:34.757135400 |
| 38 | 2013-04-01 10:56:37.887895200 | 2013-04-01 18:48:45.523163200 |
| 03 | 2013-04-05 09:50:59.676090700 | 2013-04-05 23:10:59.997149500 |
| 33 | 2013-04-08 20:04:35.081422100 | 2013-04-09 22:42:06.391661100 |
| 34 | 2013-04-14 05:37:12.330001300 | 2013-04-14 18:32:15.545483100 |
| 04 | 2013-04-15 03:28:57.726491800 | 2013-04-15 23:33:33.332273600 |
| 44 | 2013-04-17 01:42:28.476431100 | 2013-04-17 21:44:21.853923500 |
+----------+-------------------------------+-------------------------------+

Basically, i need a fliter, that can dynamically get values and extract the datetime values that fit the condition. I want to show these values in visual table and some other avg calculations with cards etc.

[JustJan]

Hi @saadnaveed ,

I think something like this can work:

Select = 
var X1 = min('Time'[Time])
var X2 = Max('Time'[Time])

var Y1 = MIN('Table'[StartTime])
var Y2 = MAX('Table'[EndTime])

var D1 = Date( YEAR(Y1), Month(Y1), DAY(Y1)) + (X1 / 24)
var D2 = Date( YEAR(Y2), Month(Y2), DAY(Y2)) + (X2 / 24)

return
IF (Y1<D2 && Y2>D1, "Selected")

It selects the desired output IDs

Hope this makes any sense 🙂

Jan if this is a solution for you, don't forget to mark it as such. thanks

[saadnaveed]

can you share .pbix with me or share some details of what you have done

[JustJan]

I hope this works (via WeTransfer)

.pbix file (until 7-Apr-2020) 

Compared to the screenshot there is not too much more happening in the file 🙂

[saadnaveed]

honestly its very helpful and have solved my issue, but i was looking for some way that not only table gets updated, but also interact with other visuals on the same page e.g a card with count of Events, their cost(avg, total) etc. can you help with that. I did found a post here with predefined timeslots but i want to do it dynamically.

[JustJan]

If you it to be truely dynamic, then (as far as you know)  you need to copy to the logic to select E_IDs in any of the measures you want to use in a visual. You can't use calculated tables since they will always produce the same result (unless you change the definition ofcourse)

For instance, something like this to calculated the selected E_ID rows. 

RowsSelected = 

var X1 = min('Time'[Time])
var X2 = max('Time'[Time])

var result =

GROUPBY(
    GroupBy (
        'Table',
        'Table'[E_ID],
        'Table'[StartTime],
        'Table'[EndTime],
        "FilterStart", MINX( CURRENTGROUP(),
                 var Y1 = 'Table'[StartTime]
                 var D1 = Date( YEAR(Y1), Month(Y1), DAY(Y1)) + (X1 / 24)
                 Return D1
                 ),

        "FilterEnd", MINX( CURRENTGROUP(),
                 var Y2 = 'Table'[EndTime]
                 var D2 = Date( YEAR(Y2), Month(Y2), DAY(Y2)) + (X2 / 24)
                 Return D2
                 )
        )
 ,

    'Table'[E_ID], "COL",SUMX( CURRENTGROUP(), 
        if (
             AND([FilterStart]<'Table'[EndTime], 
                 [FilterEnd]>'Table'[StartTime])
              , 1 -- Selected E_ID

 //
 // --- YOUR CALCULATION to calculate intermediate results (instead of the ,1)
 //

              , BLANK())

 )
)

return 
countrows(result)  

  Hope this makes any sense.

jan