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 contains an id, a start date, an end date:
ID | Start Date | End date |
12345 | 01/02/2021 | 07/02/2021 |
67890 | 01/02/2021 | 07/02/2021 |
98765 | 08/02/2021 | 14/02/2021 |
I would like to filter and display the rows for which the period I select in the filter is included in the space between start date and end date. So, for example If the filter is set on period from 01/02 to 07/02, I'll get the lines for IDs 12345 and 67890 ; if the filter is set in period 08/02 to 14/02, I'll get the line for ID 98765; If the filter is set on a period from 02/02 to 05/02 , I'll get also the lines for IDs 12345 and 67890...
I know that I can use slicer to select a date period, but it will filter only on one date column and not select the corresponding period...
Can you help me to get what I want
Solved! Go to Solution.
First create a calendar table that covers your date range. This is a calculated table, or a data source.
Table 4 = CALENDAR(min('Table (2)'[Start Date]),max('Table (2)'[End date]))
Use that table to feed your slicer.
Then create a measure to decide if you want to show a row or not
Visible :=
var a=calendar(min('Table'[Start Date]),max('Table'[End date]))
var b=CALENDAR(min('Table 4'[Date]),max('Table 4'[Date]))
var c=INTERSECT(a,b)
return if(COUNTROWS(c)>0,1,0)
and lastly add that measure as a filter to your visual and set it to "Visible is 1"
First create a calendar table that covers your date range. This is a calculated table, or a data source.
Table 4 = CALENDAR(min('Table (2)'[Start Date]),max('Table (2)'[End date]))
Use that table to feed your slicer.
Then create a measure to decide if you want to show a row or not
Visible :=
var a=calendar(min('Table'[Start Date]),max('Table'[End date]))
var b=CALENDAR(min('Table 4'[Date]),max('Table 4'[Date]))
var c=INTERSECT(a,b)
return if(COUNTROWS(c)>0,1,0)
and lastly add that measure as a filter to your visual and set it to "Visible is 1"
Wonderful, I hadn't thought about the measures at all. I feel a bit silly.
Many thanks
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 |
---|---|
5 | |
1 | |
1 | |
1 | |
1 |