Forum Discussion
Lookupmeasure based on column value
Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
- Anonymous4 years agoNot 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 SelectedKPI = SELECTEDVALUE ( 'KPI'[KPI-name] )
VAR Mrg = [Margin]
VAR SGpct = [SGpct]RETURN
SWITCH (
TRUE(),
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.