topic Re: Dynamically Reference a Measure within another Measure in DAX Commands and Tips

Dynamically Reference a Measure within another Measure

Anonymous — Thu, 07 May 2020 12:27:19 GMT

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

Re: Dynamically Reference a Measure within another Measure

Greg_Deckler — Thu, 07 May 2020 12:30:54 GMT

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

Measure = [Another measure]

VAR's are just their name

VAR __MyVar = 1
RETURN __MyVar

Re: Dynamically Reference a Measure within another Measure

Anonymous — Thu, 07 May 2020 12:44:48 GMT

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-Measures/td-p/790037

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

Thanks for your help again!

Re: Dynamically Reference a Measure within another Measure

Greg_Deckler — Thu, 07 May 2020 12:47:16 GMT

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/ba-p/279563

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

And this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titles-chart-types/m-p/1027881#M444

Re: Dynamically Reference a Measure within another Measure

Anonymous — Thu, 07 May 2020 12:59:38 GMT

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

Re: Dynamically Reference a Measure within another Measure

mahoneypat — Fri, 08 May 2020 01:48:46 GMT

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

Re: Dynamically Reference a Measure within another Measure

justinh — Thu, 18 May 2023 13:16:44 GMT

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

Re: Dynamically Reference a Measure within another Measure

jbauer22 — Thu, 22 Feb 2024 16:47:43 GMT

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)