25648

Force DAX SWITCH function to use strict (lazy) short-circuit evaluation

<h3>Question</h3>

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 () ) <hr />

Context: 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.)

<hr />

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.

<hr />

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

来源:https://stackoverflow.com/questions/61805513/force-dax-switch-function-to-use-strict-lazy-short-circuit-evaluation

Recommend

  • generic class of enum, number of values
  • UIScrollView or UICollectionView to swipe through images in order called from firebase?
  • How do I perform iterator computations over iterators of Results without collecting to a temporary v
  • Cannot getting devise omniauthable working
  • how to assign value in location array for google map?
  • Codeigniter sending email to multiple email ids, file attachment is not going with emails
  • Bluetooth on Raspberry Pi Zero W, using buildroot
  • mat tab inside tab selected index not working
  • An AppBar application not working correctly in Windows 8.x/10 (desktop working area does not get red
  • Setting headers for CakePHP Controller unit tests
  • Plotting a continuous stream of data with MatPlotLib
  • Web API Routing - Overloaded GET method causes 405 Method Not Allowed for other verbs
  • Decompress string in java from compressed string in C#
  • Scala and SBT install on Debian with Java 8
  • how to make all possible power set(or subset) from arrayList objects?
  • Setup SignalR and Service Bus inside a Azure Service Fabric service
  • c++ plugin : Is it ok to pass polymorphic objects?
  • How to add tag during runtime in C#
  • Find a directory using wildcard in Inno Setup
  • Parsing a complicated array with GetJSON Jquery
  • Existing data serialized as hash produces error when upgrading to Rails 5
  • Angular 6 illegal operation on a directory, open '/Users//.npm-global/lib/node_modules/
  • DocuSign API Replace template document but keep fields
  • Detect when MathJax has finished loading in UIWebView
  • getting the values of checkboxes in a checkboxlist control
  • Validating a Firebase Key [duplicate]
  • Nested AJAX Calls using .done
  • Python sum values in tuple in a list in a dictionary?
  • if you have a DLL creating a bitmap in memory, how to return it to the browser?
  • How to organize this layout with overflows?
  • 'float' object cannot be interpreted as an integer
  • How to select multiple items from a List view - JavaFX 8
  • How to redirect into different page by user type in php and mysql
  • Annotate objects in a queryset with next and previous object ids
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • XEP-0166: Jingle protocol implementation for voice/video chat in iOS