Define column in measure based on selected value

[MrPetz]

Hi Community,

I have a table with a lot of different columns, which I'll like to switch between in visuals. All columns are aggregated the same way, so instead of creating a switch with 50+ scenarios, I was wondering, if there's an easier way to do this.

Therefore I have tried to define a variable with the selected value of a slicer (as shown below), but I get a "column reference" error on the measure. The selected value is text formatted like "TableX[ColumnX]":

some measure =

var selectedColumn = SELECTEDVALUE( MeasureToColumn[Column] , BLANK() )
return

SWITCH(
    TRUE()
        , selectedColumn = BLANK()
            , BLANK()
        , AVERAGE(selectedColumn)
)

So, am I doing something wrong or is this simply not possible to do? Any other suggestions to accomplish the same will be appreciated.

BR Morten

[MrPetz · Accepted solution]

Update!

Based on the answer for Yulia, this is not possible to achieve in DAX.

BR Morten

[v-weiyan1-msft]

Hi @MrPetz ,

I am not sure if I understood your question correctly.
You seem to want to create a slicer to switch between different columns of data in the visual object.
The problem you are experiencing is due to the fact that you cannot use the column name directly in AVERAGE (even if it is stored as a text value in a variable.)
DAX does not support dynamic column references in this way.
Perhaps you can try the following approach to meet your needs.
Based on my understanding above, I created some data:

Please try the following steps:
1.Go to the Power query editor, select all the columns in the table , choose "Unpiovit columns".

2.Apply and close, then use the following code to create a Measure.

Measure = 
VAR Selected_Column = SELECTEDVALUE('Table'[Attribute])
RETURN
CALCULATE(AVERAGE('Table'[Value]),
          FILTER('Table', SELECTEDVALUE('Table'[Attribute]) = Selected_Column)
        )

The field of the slicer is from Table. When you select "A" in the slicer, Result is as below.

Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MrPetz]

Hi Yulia,

Thank you for the answer, and yes you have understood the question correctly. It was also my guess that it's simply not possible to do.

In regards to your proposal: That would probably work in this use case, but I want to use the same table for multiple purposes, so that's why I don't want to "unpivot" it as you propose.

BR Morten

[amitchandak]

@MrPetz , You can not use selected value in a column

I think what you are looking for can be achieved using field parameters of Calculation groups

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf
Power BI Field Parameters, Keep Axis Sort intact| Always Sort on X/Categorial Axis: https://youtu.be/GfBrB6czByw


Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

Learn Power BI: Model explorer public preview with calculation group authoring| Measure Slicer: https://youtu.be/VfxfJJ0RzvU

[MrPetz]

Hi Amit,

Thank you for the answer.

However I'm not trying to use the selected value in a column. I want to define which column to use for the calculation (in a calculated measure) based on the selection in the slicer.

As far as I can tell, I'll have to define all measures, if I use the method, you propose. If that's the alternative it would be less work to create a switch, which could handle all measures (which was what I wanted to avoid). So I don't see it as a solution for my question.

BR Morten