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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Dynamically Reference a Measure within another Measure

Does anyone know if it is possible to reference a measure dynamically within another measure? As an example, you want to bring back a measure if it matches the formatted value of a slicer. For example:

 

MasterTest =
VAR SelMeasure = SELECTEDVALUE('Measure Select'[Measure Select])
RETURN
"["&SelMeasure&"]"
 
The above obviously does not work, but I wonder if there is an equivilent of the INDIRECT() excel function?
 
I know I can do this manually, listing every measure in a SWITCH or IF function, but I have found that these functions run very slowly, to a point that would be unacceptable to the user (each measure normally takes 3 seconds to run, but putting them in a SWITCH increases it to 80 seconds) so wanted to try and calculate without a SWITCH/IF function
7 REPLIES 7
jbauer22
Frequent Visitor

We want to do this as well.  Our use case involves storing report metadata in a dimension we call "Layouts".  The Layouts dimension defines how the rows of a report will look and behave.  There are Level 1 to 3 columns on this dimension for hierachy purposes, but we also have CalcType and Calc1 to 4 columns.  The CalcType dictate the behaviour of the measure based using DAX.  Here are examples of CalcType behaviour we are testing now:

0 = Return Blank (for reports that need a blank row as a separator)
1 = Use foreign key listed for that row (e.g. Account Number)
2 = Use range of values
3 = Use measure (this is where your solution would be helpful - we would essentially specify a measure in the Calc1 field.)
4 = Divide (E.g. sum two separate values for a level and divide them - this is great for getting things like % of Revenue at a row level)

Greg_Deckler
Super User
Super User

Yes, you can reference a measure in a measure like:

Measure = [Another measure]

VAR's are just their name

VAR __MyVar = 1
RETURN __MyVar

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks for the reply Greg. I may be that i'm not smart enough to understand your reply, but I can't see how i can make that dynamically reference multiple measures?

 

The VAR would still be static and referencing a single measure wouldnt it? I want the user to be able to choose multiple measures. The measures will then sit in the columns of a matrix.

 

I'm essentially trying to achieve the solution to this post:

https://community.powerbi.com/t5/Desktop/Dynamic-Column-Names-for-Matrix-Visual-using-Sliced-Measure...

 

But without Switch/IF, given the slowdown they create.

 

Thanks for your help again!

OK, that definitely clarifies things. You need a disconnected table I believe. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

 

Also, see this: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

 

And this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titl...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks again Greg. I think this is essentially what i've already done, but in those links you sent over, you still need to do an IF/SWITCH statement in the end to ultimately tie the measures together. I was looking at a way around this, and thought if it could dynamically reference a measure based on text, then i could skip using SWITCH altogether

I don't think what you are describing is possible in DAX.  For example, if you could have a table of labels and DAX expressions as text, you could use the label column in your slicer and the "measure" would execute the DAX expression for that row.  That doesn't exist as far as I know.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


It would be nice to be able to reference measures within another measure using dynamic text strings. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors