Forum Discussion

kpost's avatar
kpost
Icon for Solution Sage rankSolution Sage
2 years ago
Solved

Seeking clarity on optimization re: Variables, Switch Statements

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.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi kpost ,

     

    In DAX, variables () are evaluated once at the point they are defined in the formula. This means that all variables in your second are calculated regardless of the statement's outcome.

    Given this, your assumption is correct. The first approach, where the measures and are calculated directly within the statement based on the slicer selection, is more efficient. This is because, in this scenario, only the measure corresponding to the selected option is calculated.

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi kpost ,

     

    In DAX, variables () are evaluated once at the point they are defined in the formula. This means that all variables in your second are calculated regardless of the statement's outcome.

    Given this, your assumption is correct. The first approach, where the measures and are calculated directly within the statement based on the slicer selection, is more efficient. This is because, in this scenario, only the measure corresponding to the selected option is calculated.

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.