Dynamically Reference a Measure within another Measure

[Anonymous]

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

[jbauer22]

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]

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

Measure = [Another measure]

VAR's are just their name

VAR __MyVar = 1
RETURN __MyVar

[Anonymous]

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!

[Greg_Deckler]

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

[Anonymous]

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

[mahoneypat]

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

[justinh]

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