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 looking for a solution to filtering a dataset that contains start and end dates based on a user selecting one date.
Below is a example of the type of data that am working with.
Below is a sample report.
In this example, we want to answer the question "Who was working where on June 1, 2022?"
I want the users to pick one and only one date. The users will want to change and rechange this date easily. So, I'd prefer it to be in a slicer. I want to use only on the data where the start comes on or before the user selected date and the end date is on or after the user selected date.
If the users see something incorrect, they will go into the application and make corrections. They will rerun the report and expect see their changes immediately. So, this must work with DirectQuery data.
My actual dataset is much more complex. But this example gets to the heart of the issue.
Solved! Go to Solution.
Hi, @Gary_in_Ohio
You can create a measure like:
filter =
IF (
MAX ( Location[StartDate] ) <= SELECTEDVALUE ( AsOf[Date] )
&& MAX ( Location[EndDate] ) >= SELECTEDVALUE ( AsOf[Date] ),
1,
0
)
Then you can apply this mesaure to the table visual filter pane.
Best Regards,
Community Support Team _ Eason
Hi, @Gary_in_Ohio
You can create a measure like:
filter =
IF (
MAX ( Location[StartDate] ) <= SELECTEDVALUE ( AsOf[Date] )
&& MAX ( Location[EndDate] ) >= SELECTEDVALUE ( AsOf[Date] ),
1,
0
)
Then you can apply this mesaure to the table visual filter pane.
Best Regards,
Community Support Team _ Eason
@Gary_in_Ohio ,refer
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |