Forum Discussion

Stcorda's avatar
Stcorda
Frequent Visitor
2 years ago
Solved

Switch column value selecting from slicer

Hi, I am looking for a way to switch column name & value according to the slicer selection.  To switch value i created this measure:   YTD Reference = SWITCH (VALUES('Scenari'[Reference Scenario]...
  • jdbuchanan71's avatar
    2 years ago

    You can do that by creating a field parameter for each of the sets of measures in your slicers.

    https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

    Go to Modeling > New parameter > Fields

    Name the parmeter and add the measures you want in that selection.

    Add the paramater to a slicer and to to your visual.  I would set the slicer to drop down single select.  When you change the measure selected in the slicer the column name changes in the table.

     

     

  • jdbuchanan71's avatar
    2 years ago

    Sure, this is going to look more complicated that it really is probably.  There is a bug? with field parameters where you cannot use SELECTEDVALUE to read the selection.  Here is an article on it: https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/

    That means we have to use the longer SELECTCOLUMNS SUMMARIZE route shown in that article. 

    We use that to see what is selected in the Reference Scenario and Comparison Scenario 1 then use SWITCH to get those amounts and finally do the compare.

    YTD Ref - Comp 1 = 
    VAR _RefSelection =
        SELECTCOLUMNS (
            SUMMARIZE (
                'Reference Scenario',
                'Reference Scenario'[Reference Scenario],
                'Reference Scenario'[Reference Scenario Fields]
            ),
            'Reference Scenario'[Reference Scenario]
        )
    VAR _CompSelection =
        SELECTCOLUMNS (
            SUMMARIZE (
                'Comparison Scenario 1',
                'Comparison Scenario 1'[Comparison Scenario 1],
                'Comparison Scenario 1'[Comparison Scenario 1 Fields]
            ),
            'Comparison Scenario 1'[Comparison Scenario 1]
        )
    VAR _Ref =
        SWITCH (
            _RefSelection,
            "YTD ACT", [YTD ACT],
            "YTD FCST (6+6)", [YTD FCST (6+6)],
            "YTD FCST (9+3)", [YTD FCST (9+3)],
            "YTD FCST (3+9)", [YTD FCST (3+9)],
            "YTD BDG", [YTD BDG]
        )
    VAR _Comp =
        SWITCH (
            _CompSelection,
            "YTD ACT", [YTD ACT],
            "YTD FCST (6+6)", [YTD FCST (6+6)],
            "YTD FCST (9+3)", [YTD FCST (9+3)],
            "YTD FCST (3+9)", [YTD FCST (3+9)],
            "YTD BDG", [YTD BDG]
        )
    RETURN
        _Ref - _Comp

     

    Please note, it will look like the DAX is throwing an error, even though it works fine: