Switch column value selecting from slicer

[Stcorda]

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]),

"Act",[YTD ACT],

"F(6+6)",[YTD FCST (6+6)],

"F(9+3)",[YTD FCST (9+3)],

"F(3+9)",[YTD FCST (3+9)],

"BDG",[YTD BDG]

)

and it seems work for values, but i missed the name in the table. Could you suggest me an alternative so that the header name and value in the table changes according to the slicer selection?

e.g. YTD Reference field in the table should display YTD ACT as selected in the slicere

thanks a lot!

[jdbuchanan71 · Accepted solution]

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]

Here is my sample file for you to take a look at.

[jdbuchanan71 · Accepted solution]

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:

[jdbuchanan71]

Because you can select more that 1 item to be visable from a field prameter you can't do math on them directly.  You would have to write a measure that reads the selection in the field paramaters and returns those measures to do the compare on.

[Stcorda]

Ok, got it.. May i ask you a suggestion on dax code formula which can be used? Thanks in advance, I am trying to walk through first times

[jdbuchanan71 · Accepted solution]

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.

[Stcorda]

Thanks! it helps a lot, may i do arithmetic between parameters?