Forum Discussion
Create measure as a filter to dynamically filter data based on slicer selection
Hi Community!
Have tricky task to filter chart based on dynamic slicer selection with conditions. In slicer I have Scenario label from scenarios table (see table below and image), each label value has ID and Date.
What I'm trying to achieve create measure as filter for chart - when selected some value in scenario slicer (only single select) it shoul check it date or ID and show 1 for all values >= selected scenarioDate (or <= selected scenarioID) and 0 for others. Visual interactions between slicer and chart are disabled.
| ScenarioID | Scenario | ScenarioDate |
| 2 | This Week | 10/9/2023 12:00:00 AM |
| 3 | Last Week | 10/2/2023 12:00:00 AM |
| 4 | 09/25/2023 | 9/25/2023 12:00:00 AM |
| 5 | 09/18/2023 | 9/18/2023 12:00:00 AM |
| 6 | 09/11/2023 | 9/11/2023 12:00:00 AM |
| 7 | 09/04/2023 | 9/4/2023 12:00:00 AM |
| 8 | 08/28/2023 | 8/28/2023 12:00:00 AM |
| 9 | 08/21/2023 | 8/21/2023 12:00:00 AM |
| 10 | 08/14/2023 | 8/14/2023 12:00:00 AM |
expected result
Or maybe there is better way to do this not use measure as a filter on visual?
I've went with onother way calculate values based on filter contex... fynny thing my ScenarioID have int type but filter does not see it and when I set variable to 5 averything working. Where is problem?
__TesRev1 =
var _vSelectedScenario = SELECTEDVALUE( ScenarioWeekly[ScenarioID])
return
CALCULATE( SUM( ScenarioDataWeekly[BookingRevenue]), FILTER( ScenarioWeekly, ScenarioWeekly[ScenarioID] <= _vSelectedScenario))
not working properly
var _vSelectedScenario = 5
return
CALCULATE( SUM( ScenarioDataWeekly[BookingRevenue]), FILTER( ScenarioWeekly, ScenarioWeekly[ScenarioID] <= _vSelectedScenario))
working ok
Hi @Ibendlin, I have complex data model with 3 fact tables 2 period slicers and 2 scenario slicers, can't use this approach as slicer should be synched across all pages and there is logic based on it in measures. But I've managed to find out anouther solution - create calculated table based on GENERATESERIES, use it as a slicer and calculations so user can have possibility choose how many scenarios wants to see regardless selected scenario in slicer.
2 Replies
- lbendlinSuper User
Don't mess with the interactions. Instead, use a disconnected table for the slicer and your measure based on its selection.
- bsasPost Patron
Hi @Ibendlin, I have complex data model with 3 fact tables 2 period slicers and 2 scenario slicers, can't use this approach as slicer should be synched across all pages and there is logic based on it in measures. But I've managed to find out anouther solution - create calculated table based on GENERATESERIES, use it as a slicer and calculations so user can have possibility choose how many scenarios wants to see regardless selected scenario in slicer.