Forum Discussion
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:
7 Replies
- Yes, you can reference a measure in a measure like:
Measure = [Another measure]
VAR's are just their name
VAR __MyVar = 1
RETURN __MyVarThanks 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:
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/ba-p/279563
Also, see this: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428
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)
