topic Re: SWITCH and IF always doing eager evaluation in DAX Commands and Tips

SWITCH and IF always doing eager evaluation

trattnh — Wed, 04 Sep 2024 14:31:10 GMT

From what I read online switch does eager evaluation and IF does lazy evaluation.
I have defined both options. Both take the same amount of time (I analyzed this in dax studio).

In the server timings it only actually evaluates a single measure but if I comment out measure2 and measure3 and leave only measure1 which is the selected_measure_name - it reduces the speed by 2/3. If I comment ony one, the speed is reduced by 1/3 - so it seems to be evaluating nevertheless.

Also, if I return some hardcoded string instead of "measure_value" nothing is evaluated.

I am confused by this behavior of the query.

Either it is doing eager evaluation or it isn't...

VAR selected_measure_name = LOOKUPVALUE(my_table[measure_name], my_table[field_name], SELECTEDVALUE(my_table[field_name]))
VAR measure_value = SWITCH (TRUE(),
selected_measure_name = "measure1", [measure1],
selected_measure_name = "measure2", [measure2],
selected_measure_name = "measure3", [measure3]
BLANK()
)
return measure_value

VAR selected_measure_name = LOOKUPVALUE(my_table[measure_name], my_table[field_name], SELECTEDVALUE(my_table[field_name]))
return IF(selected_measure_name = "measure1", [measure1],
IF(selected_measure_name = "measure2", [measure2],
IF(selected_measure_name = "measure3", [measure3],
BLANK())))
return measure_value

Re: SWITCH and IF always doing eager evaluation

mark_endicott — Wed, 04 Sep 2024 15:04:02 GMT

trattnh - I'm not sure if this a query or if you are providing information. But yes SWITCH and IF will likely use the same query plan if the Vertipaq engine evaluation decides EAGER is the best option. You can force IF to do an EAGER evalution with IF. EAGER

Incidentally, you probably dont need the LOOKUPVALUE in your selected_measure_name variable. If there is one "measure" per "field_name" you can just use MAX( my_table[measure_name] ) because however you are selecting field_name the MAX will always grab the appropriate measure_name from the row of this table. This version should be slightly quicker in DAX Studio.

You can find more information here: https://www.sqlbi.com/articles/understanding-eager-vs-strict-evaluation-in-dax/

Re: SWITCH and IF always doing eager evaluation

trattnh — Thu, 05 Sep 2024 05:43:52 GMT

This is a query. My issue is that assuming that measures 1, 2, 3 all take the same time to execute, the SWITCH or IFstatement should take the same time to execute regardless of how many options I have in them. In reality, the time increases for every option I add. If I add options for measures 4, 5, 6 the statement will take twice the amount of time than if I only had measures 1, 2, 3, even though only a single measure should be evaluated according to the value of selected_measure_name.
In effect, I am trying to write a measure that "gets as input" another measure by name and then returns the value of that measure - this is in order to prevent the need to duplicate complex measures where the only difference is the name of an internal measure it is calling

Re: SWITCH and IF always doing eager evaluation

mark_endicott — Thu, 05 Sep 2024 08:27:17 GMT

trattnh - I'm sorry, I dont know of a way you can re-write this to see if you can force a strict evaluation.

Have you thought about using a field parameter to switch between the measures instead?

Re: SWITCH and IF always doing eager evaluation

trattnh — Thu, 05 Sep 2024 11:06:06 GMT

Actually, I need to do something similar to displaying a table visual which has two columns - measure name, measure value.