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.
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.
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")
can you share .pbix with me or share some details of what you have done
I hope this works (via WeTransfer)
Compared to the screenshot there is not too much more happening in the file 🙂
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.
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
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |