Forum Discussion

bsas's avatar
bsas
Post Patron
2 years ago
Solved

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 ScenarioScenarioDate
2This Week10/9/2023 12:00:00 AM
3Last Week10/2/2023 12:00:00 AM
409/25/2023     9/25/2023 12:00:00 AM
509/18/20239/18/2023 12:00:00 AM
609/11/20239/11/2023 12:00:00 AM
709/04/20239/4/2023 12:00:00 AM
808/28/20238/28/2023 12:00:00 AM
908/21/20238/21/2023 12:00:00 AM
1008/14/20238/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

 

  • bsas's avatar
    bsas
    2 years ago

    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

  • Don't mess with the interactions. Instead, use a disconnected table for the slicer and your measure based on its selection.

    • bsas's avatar
      bsas
      Post 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.