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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Lookupmeasure based on column value

Hi!

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 🙂

2 REPLIES 2
amitchandak
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.

Anonymous
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 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.




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.