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 am struggling with the problem of creating a filter / slicer that returns events that either starts, ends, or spans a time interval. Each event has a defined start time and end time, and I also have a Date table and a Month table.
Re: figure below - when the month of March is selected, events A, B, C and E should be returned, but D and F should not.
Solved! Go to Solution.
Hi @brunborg ,
Firstly, remove the relationship between Event table and data table, create relationship between data table and month table.
The create a measure like below:
Measure =
IF (
MIN ( Events[Start Date] ) IN VALUES ( 'Date'[Date] )
|| MIN ( Events[End Date] ) IN VALUES ( 'Date'[Date] )
|| (
MIN ( Events[Start Date] ) < MIN ( 'Date'[Date] )
&& MIN ( Events[End Date] ) > MAX ( 'Date'[Date] )
),
1,
0
)
Then drag this measure to FILTERS, set it like below:
The result will like below:
Best Regards,
Teige
Hi @brunborg ,
Firstly, remove the relationship between Event table and data table, create relationship between data table and month table.
The create a measure like below:
Measure =
IF (
MIN ( Events[Start Date] ) IN VALUES ( 'Date'[Date] )
|| MIN ( Events[End Date] ) IN VALUES ( 'Date'[Date] )
|| (
MIN ( Events[Start Date] ) < MIN ( 'Date'[Date] )
&& MIN ( Events[End Date] ) > MAX ( 'Date'[Date] )
),
1,
0
)
Then drag this measure to FILTERS, set it like below:
The result will like below:
Best Regards,
Teige
Hi,
I've got the same data structure in various databases and this solution works but...
It takes a looooong time to filter when aplying to not so large tables. I`ve got a table with no more than 5k records and when I try to filter the data the process starts but never ends with the result.
Is there any other more efficient approach to get this done? I have to use the same technique with other databases that reach to more than 1M records and this solution doesn't seem to be useful in that case.
Thank you!
@brunborg add new measure as belo and then you can use this measure in visual level filter where value = 1
Filter Based on Date = IF(
MIN( Table1[Start Date] ) IN VALUES( 'Calendar'[Date] ) ||
MIN( Table1[End Date] ) IN VALUES ( 'Calendar'[Date] ) , 1 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |