title: Force DAX SWITCH function to use strict (lazy) short-circuit evaluation
body: <p><strong>Set up:</strong>
  Similar to <a href="https://social.msdn.microsoft.com/Forums/en-US/92acbe55-242f-4578-99b1-fc9464e425ea/dax-not-performing-shortcircuit-evaluations-for-the-if-function" rel="noreferrer">this question on a MSDN forum</a>, 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):</p>
  <pre><code>VariableMeasure =
  VAR ReturnType =
      SELECTEDVALUE ( ParamReturnType[ReturnType] )
  SWITCH (
      ReturnType,
      &quot;NAV&quot;, [Nav],
      &quot;Income&quot;, [Income],
      &quot;ROI&quot;, [Roi],
      &quot;BM&quot;, [Benchmark],
      BLANK ()
  )
  </code></pre>
  <hr />
  <p><strong>Context:</strong>
  The reason for the switching measure is to have the ability to choose which measures to display on a report by choosing the <code>ReturnType</code> 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:</p>
  <p><a href="https://i.stack.imgur.com/Kvc31.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Kvc31.png" alt="Matrix Visual" /></a></p>
  <p>(As you can see, it's not showing <code>BM</code> since it's not selected.)</p>
  <hr />
  <p><strong>Problem:</strong> The problem is that when I just have <code>NAV</code> 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 <code>[Nav]</code> 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.</p>
  <hr />
  <p><strong>Research:</strong>
  I've read the following SQL BI articles, which mention lazy/strict evaluation and short-circuiting.:</p>
  <p><a href="https://www.sqlbi.com/articles/understanding-eager-vs-strict-evaluation-in-dax/" rel="noreferrer">Understanding eager vs. strict evaluation in DAX</a></p>
  <p><a href="https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/" rel="noreferrer">Optimizing IF and SWITCH expressions using variables</a></p>
  <p>My measure is analogous to the last example in the second link but does not benefit from &quot;short-circuit evaluation&quot; as it does in their case.</p>
  <p><a href="https://community.powerbi.com/t5/Desktop/SWITCH-performance-problem/m-p/847199" rel="noreferrer">This Power BI Community question</a> is similar but provides no additional insight.</p>
  <p><a href="https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&amp;MessageKey=7c0622bf-ebc4-4dd2-9d95-6550e148e990#bme8b24d5d-7c75-4a69-b8b4-89e35d604c91" rel="noreferrer">This Power BI Usergroup Community post</a> references a couple more interesting articles but those haven't ultimately led me to a resolution, unfortunately.</p>
  <hr />
  <p><strong>Question:</strong> How can I get each measure in the <code>SWITCH</code> to evaluate independently of the other cases since building a query plan to accommodate all of the cases simultaneously results in poor performance everywhere?</p>
  <p>I'm open to suggestions or workarounds that resolve my issue even if it doesn't narrowly answer this question.</p>
  <hr />
  <p><strong>Additional information:</strong> 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).</p>
  <hr />
  <p><strong>Update:</strong></p>
  <p>Microsoft has addressed this problem at least to some extent in a May 2021 update of Power BI:</p>
  <p><a href="https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-may-2021-feature-summary/#perf" rel="noreferrer">https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-may-2021-feature-summary/#perf</a></p>