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.
Greetings.
I have a Table 'C' what contains two columns 'colStart' and 'colEnd'. It's a Date Range.
What I need is a slider that allows to select a dates range and filter 'C' table showing only those rows that have any kind of overlapping with it.
To create the slider I just created a table using CALENDAR:
Calendar = CALENDAR(FIRSTDATE(C[colStart])|LASTDATE(C[colEnd]))
Thanks to https://community.powerbi.com/t5/Desktop/Calculate-days-from-filter-overlapping-date-ranges/td-p/216... I already have a Measure that identifies that, but I can't filter the table using it:
Measure:
in_range = var startD = FIRSTDATE(ALLSELECTED('CALENDAR'[Date])) var endD = LASTDATE(ALLSELECTED('CALENDAR'[Date])) var finiv = MAX(C[colStart]) var ffinv = MAX(C[colEnd]) return (finiv >= startD && finiv <= endD) || (ffinv >= startD && ffinv <= endD) || (finiv < startD && ffinv > endD)
Measure result:
I want to filter 'C' table, showing only where in_range is True. This is because I have related tables with 'C' that also need be filtered.
Hope you can help me. Tell me if you don't understand something. I have a test .pbix file but i don't know if it's possible to upload here.
Thanks!
Solved! Go to Solution.
It is impossible to create a new table physically (click on "New Table") and make it change with slicer.
You could use a table visual and make the table visual change with the slicer.
1.Create a calendar date table
calendar = CALENDAR(MIN(Table1[colStart]),MAX(Table1[colEnd]))
Edit relationship with "calendar" table and data table based on 'calendar' [Date] to [colStart] and 'calendar' [Date] to [colEnd]
2. create measures
create measures in the 'calendar' table
min = MIN('calendar'[Date]) max = MAX('calendar'[Date])
create measures in the data table
flag = IF(MAX(Table1[colStart])<=[max]&&MAX(Table1[colEnd])>=[min],1,0)
3. add 'calendar' [Date] to the slicer, then add 'Table1'[flag] to the Visual Level filter and select "show items when value is 1"
Best regards
Maggie
It is impossible to create a new table physically (click on "New Table") and make it change with slicer.
You could use a table visual and make the table visual change with the slicer.
1.Create a calendar date table
calendar = CALENDAR(MIN(Table1[colStart]),MAX(Table1[colEnd]))
Edit relationship with "calendar" table and data table based on 'calendar' [Date] to [colStart] and 'calendar' [Date] to [colEnd]
2. create measures
create measures in the 'calendar' table
min = MIN('calendar'[Date]) max = MAX('calendar'[Date])
create measures in the data table
flag = IF(MAX(Table1[colStart])<=[max]&&MAX(Table1[colEnd])>=[min],1,0)
3. add 'calendar' [Date] to the slicer, then add 'Table1'[flag] to the Visual Level filter and select "show items when value is 1"
Best regards
Maggie
The table can be filtered like this:
VAR mnDte = MIN( 'Calendar'[Date] )
VAR mxDte = MAX( 'Calendar'[Date] )
RETURN
CALCULATETABLE(
DateRanges,
DateRanges[colEnd] >= mnDte,
DateRanges[colStart] <= mxDte
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks @LivioLanzo but I have created new table with that formula and does not work. It never changes when updating slicer.
It's like MIN( 'Calendar'[Date] ) is returning the lowest date in the table when it should returns the lowest filtered date by slicer. Same with MAX. (my assumptions)
Other suggestion?
Thanks
In other post, I see that @v-ljerr-msft said:
"Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.
So it is not possible to create a calculate column/table can change dynamically with user selections on the report. (...)"
Now I'm totally lost...
How could I achieve the goal then?
in fact the table I have posted is not meant to be used as a physical table.
It is a table that is calculated on the fly in your measure as, from what I understood, you want to use it as a filter for another measure
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sorry @LivioLanzo
What I want to filter is 'C' table. Same behaviour like if I select an option in a slicer that it filters the table.
@javi0unavailabl Would your first solution not work then if instead of using the in_range measure in the values section of the matrix, you use it in the filter section of the visual and set it equal to TRUE ?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@LivioLanzo No, I can't select any from measure filter (it shows with no options) and also can't drag the measure into Page level filter.
Also... I would to filter the table no a visual .
Thanks
Anyone have another idea, please?
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |