Showing results for 
Search instead for 
Did you mean: 
Not applicable

Lookupmeasure based on column value


Is there any way of referencing a measure in DAX without explicitly writing the measure name?


I want to achieve the following:
DisplayMeasure = LOOKUPMEASURE ( SELECTEDVALUE ('Measuretable'[Measurename]) )
(lookupmeasure is not a function, but is the best way I can describe what I want to achieve).

I want the DisplayMeasure to automaticaly pick the right underlying measure based on the values that exists in a parameter-table in the model. Now I have the desired functionality by defining all the measures as variables in the measure and then using a switch-statement to dynamicaly display the right measure based on the users selection. The caviat by doing it this way is that I always have to pass new values in the parameter table into the measure for it to be able to find the right measure.
It should be a better way of dynamically fetch the text in the row for the column containing the measure name, and call the measure based on this name.

Thanks for all help 🙂

Super User
Super User

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Not applicable

Of course. Let me elaborate.

I've got a KPI table that lists a lot of KPIs in one column by its name. In another column in the same table, the KPIs corresponding measure is stored in clear text. As there is a lot of KPIs and the list is growing, I want to create a functionality where a user can pick a KPI in a slicer by its name, and the page shows results for the underlying measure (in graphs and columns).

I've got this functionality to work by explicitly declaring measures as variables in dax and using the switch function mapping the users KPI-selection to its underlying measure.
The problem is that I constantly have to update the measure as the parameter table grows, adding new variables and mapping them in the switch-function in dax.

All this would be avoided if I found a way for DAX to pick up the text-value that is already stored in the table in the measures-column and point to the identicaly named measure in the model.

KPI-name KPI measure
Margin [Margin]
Sales growth[SGpct]

If I could get the DAX expression to dynamicaly map the two columns, that would be very helpful.

So, today I have to write the DAX like this (note, the list of kpis is much longer):

VAR Mrg = [Margin]
VAR SGpct = [SGpct]

SelectedKPI = "Margin", Mrg,
SelectedKPI = "Sales growth", SGpct,

Is there a way of getting DAX to lookup the measure based on what text is in a cell in a column?
Like in the example over, instead of VAR Mrg = [Margin] it would be VAR Measure = [GET THE SELECTED VALUE IN COLUMN "KPI measure"]. Only need for one variable.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors