Let's say there is a single-select slicer with the following options from a column, 'Selection_Options'[Options]:
"Inbound"
"Outbound"
"Total"
In an accompanying line graph visual, depending on the slicer selection, a measure with a switch statement will be used to display a different measure.
Here is the accompanying measure used in the visual.
Switch Measure =
VAR Selection = SELECTEDVALUE('Selection_Options'[Options])
RETURN
SWITCH(
TRUE(),
Selection = "Inbound", [Received],
Selection = "Outbound", [Shipped],
Selection = "Total", [Total]
)
QUESTION:
Is this more efficient than having each measure calculated as a variable and then returning the correct value, as below:
Switch Measure =
VAR Selection = SELECTEDVALUE('Selection_Options'[Options])
VAR inbound = [Received]
VAR outbound = [Shipped]
VAR total = [Total]
RETURN
SWITCH(
TRUE(),
Selection = "Inbound", inbound,
Selection = "Outbound", outbound,
Selection = "Total", total
)
Since this is a switch statement, I'm assuming the first one is more efficient because each of the three measures would only be calculated if the selection matches what is chosen in the slicer. But what I don't know for sure is whether every variable in a DAX query is calculated every time, or if they are only calculated if they are needed during evaluation.
What is the order of operations here in terms of how Power BI processes DAX queries? I love using variables to increase readability of my DAX queries, but sometimes (situations like this), it seems like it will interfere with performance. Any insight would be helpful, thank you.
