Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to create a nested filter/slicer?

Hi all,

 

I have like 50 scenarios, each of which has multiple parameters, and I want to create a measure, that I first select a scenario as base case, and choose which value I want to compare, and generate a visual. I have created 3 tables, Base case table for a base case slicer, value table contains the values of each parameter of each case, parameter table for a parameter slicer.

The tables I have have a structure like below:

Base case table

Case 1
Case 2
Case 3
Case 4

 

Value table, For each case, it has a table of all kinds of parameters with values

parameter 1parameter 2parameter 3
357
468

 

Parameter table

parameter 1
parameter 2
parameter 3
parameter 4

 

I have manually did a meausure for a specific parameter like this:

Total Tonnes % difference from Base Case =
VAR Base_Case = SELECTEDVALUE('Scenario Definition'[Base_Scenario])
VAR __BASELINE_VALUE =
    CALCULATE(
        SUM('Input Files'[Total Tonnes]),
        'Input Files'[Scenario_Name] IN { Base_Case }
    )
   
VAR __MEASURE_VALUE = SUM('Input Files'[Total Tonnes])
RETURN
    IF(
        NOT ISBLANK(__MEASURE_VALUE),
        DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
    )

 

 

Above code works for a specific parameter "Total Tonnes", but I want to have something that can dynamically change as I choose different parameters.

I tried this, basically I want the SUM function SUM('Input Files'[parameter]), call the parameter slicer, but it does not work.

Total parameter % difference from Base Case =
VAR Base_Case = SELECTEDVALUE('Scenario Definition'[Base_Scenario])
VAR Selected_parameter = SELECTEDVALUE('Parameters'[Name])
parameter == VAR Selected_parameter
VAR __BASELINE_VALUE =
    CALCULATE(
        SUM('Input Files'[parameter]),
        'Input Files'[Scenario_Name] IN { Base_Case }
    )
   
VAR __MEASURE_VALUE = SUM('Input Files'[parameter])
RETURN
    IF(
        NOT ISBLANK(__MEASURE_VALUE),
        DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
    )

 

Would you please help out?

 

Thank you.

Need Help

1 Reply

  • freginier's avatar
    freginier
    Solution Sage
    3 months ago

    Hi,

     

    The reason your second measure doesn't work is that DAX cannot accept a column name as a variable - SUM('Input Files'[parameter]) is interpreted literally, looking for a column called "parameter" rather than the column whose name lives in the [Selected_parameter] variable. Three clean ways to do what you want, in order of effort vs scalability:

     

    A) Unpivot the parameter columns once in Power Query (cleanest, scales to N parameters).

     

    Reshape 'Input Files' from:

    Scenario | Total Tonnes | Cost | OPEX | ...

    to:

    Scenario | Parameter | Value

     

    In Power Query: select all parameter columns > Transform > Unpivot Columns. Build a 'Parameters' table with one row per Parameter name, related to 'Input Files'[Parameter].

     

    Then a single measure handles every parameter:

     

    % vs Base =

    VAR Base = SELECTEDVALUE('Scenario Definition'[Base_Scenario])

    VAR Baseline = CALCULATE(SUM('Input Files'[Value]), 'Input Files'[Scenario_Name] = Base)

    VAR Current  = SUM('Input Files'[Value])

    RETURN DIVIDE(Current - Baseline, Baseline)

     

    The Parameter slicer filters which rows the measure aggregates, the Base case slicer drives Base, and you don't need a per-parameter measure anywhere.

     

    B) SWITCH on a parameter table (keeps your wide schema):

     

    Selected % vs Base =

    VAR Base = SELECTEDVALUE('Scenario Definition'[Base_Scenario])

    VAR P    = SELECTEDVALUE('Parameters'[Name])

    VAR Current =

      SWITCH(TRUE(),

        P = "Total Tonnes", SUM('Input Files'[Total Tonnes]),

        P = "Cost",         SUM('Input Files'[Cost]),

        P = "OPEX",         SUM('Input Files'[OPEX]))

    VAR Baseline =

      SWITCH(TRUE(),

        P = "Total Tonnes", CALCULATE(SUM('Input Files'[Total Tonnes]), 'Input Files'[Scenario_Name] = Base),

        P = "Cost",         CALCULATE(SUM('Input Files'[Cost]),         'Input Files'[Scenario_Name] = Base),

        P = "OPEX",         CALCULATE(SUM('Input Files'[OPEX]),         'Input Files'[Scenario_Name] = Base))

    RETURN DIVIDE(Current - Baseline, Baseline)

     

    C) Use the built-in Field Parameters feature (Modeling > New parameter > Fields). It auto-generates the SWITCH measure and gives users a clean dropdown without writing any DAX. Fastest path on a recent Power BI Desktop build.

     

    Approach A scales best. B is quickest to retrofit on an existing wide model. C is lowest-effort if Field Parameters are enabled in your tenant. With 50 scenarios and growing, A will save you most pain long-term.