Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Define column in measure based on selected value

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Update!

 

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

 

BR Morten

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Update!

 

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

 

BR Morten

v-weiyan1-msft
Community Support
Community Support

Hi @Anonymous ,

 

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:

vweiyan1msft_0-1706529606114.png

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

vweiyan1msft_1-1706529625774.png

vweiyan1msft_2-1706529634235.png

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.

vweiyan1msft_3-1706529680240.png

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.

Anonymous
Not applicable

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
Super User
Super User

@Anonymous , 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-fc4095ae9afd
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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.