Set up: Similar to this question on a MSDN forum, I have a measure that switches between various other measures (some of them much more complex than others). The measure looks like this (my actual measure has more cases):
VariableMeasure = VAR ReturnType = SELECTEDVALUE ( ParamReturnType[ReturnType] ) SWITCH ( ReturnType, "NAV", [Nav], "Income", [Income], "ROI", [Roi], "BM", [Benchmark], BLANK () )
The reason for the switching measure is to have the ability to choose which measures to display on a report by choosing the
ReturnType with a slicer and then the selected measures show up as column headers in a matrix visual. For example, my matrix may look like this:
(As you can see, it's not showing
BM since it's not selected.)
Problem: The problem is that when I just have
NAV selected the measure is still nearly as slow as with everything selected despite it being a cheap measure to compute. When I profile the query with DAX Studio, it takes about 2.7 seconds for what should be a simple query. Indeed, if I comment out the lines with measures other than
[Nav] in the switching measure the performance improves drastically, running in less than 100 milliseconds (30x faster). DAX Studio shows that the formula engine (FE) is responsible for about 99.5% of the 2.7 seconds whereas the storage engine (SE) uses less than 20 milliseconds.
Research: I've read the following SQL BI articles, which mention lazy/strict evaluation and short-circuiting.:
Understanding eager vs. strict evaluation in DAX
Optimizing IF and SWITCH expressions using variables
My measure is analogous to the last example in the second link but does not benefit from "short-circuit evaluation" as it does in their case.
This Power BI Community question is similar but provides no additional insight.
This Power BI Usergroup Community post references a couple more interesting articles but those haven't ultimately led me to a resolution, unfortunately.<hr />
Question: How can I get each measure in the
SWITCH to evaluate independently of the other cases since building a query plan to accommodate all of the cases simultaneously results in poor performance everywhere?
I'm open to suggestions or workarounds that resolve my issue even if it doesn't narrowly answer this question.<hr />
Additional information: I have a hunch that the query engine may indeed be strictly evaluating the switch function but only after building a generalized query that can support all of the cases and since the different measures are built with dissimilar logic, this is highly inefficient (and also where my situation is not analogous to the SQL BI example I referred to).