The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Let's say I have a Switch function that will return different variable based on certain conditions:
Measures =
var a = (some complex calc)
var b = (some complex calc)
var c = (some complex calc)
var d = (some complex calc)
RETURN
SWITCH(TRUE(),
(condition 1), a,
(condition 2), b,
(condition 3), c,
(condition 4), d
)
Which will be triggered first, var a or the Switch function?
I need to know for performance purpose. If all the variables will still get calculated first (since they're written before the RETURN SWITCH function), then performance will be bad depending on how complex the calculations are. But if it ignores all those first, go to RETURN SWITCH, then decide which var to calculate, then it's good for performance.
Hello @sahabatsuria ,
I recommend to check this video to learn more about variables and how they work
https://youtu.be/Gr8LjtnGNlA?si=DNM3l03PVfTmAv2y
Proud to be a Super User! | |
Hi @Idrissshatila , while the video explain how variable works, it doesn't actually answer my question in terms of the execution. If I miss it, can you please let me know the timestamp so that I can review it again?
In DAX, variables declared within a measure are evaluated only when they are referenced, and not when they are declared. This means that DAX will not calculate all the variables (a, b, c, d) in advance before the SWITCH function. Instead, the evaluation will happen conditionally, based on the logic inside the SWITCH function.
In your case, the SWITCH function will be evaluated first to determine which condition is TRUE(). Once a matching condition is found, only the corresponding variable (a, b, c, or d) will be calculated.
For example:
Thus, for performance, this approach is efficient because the complex calculations will only be executed when needed, based on the result of the SWITCH function. So, it won't affect performance negatively as long as your conditions are properly defined.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in
In DAX (Data Analysis Expressions) for Power BI, variables are calculated before the RETURN statement. When you define a variable using the VAR keyword, its value is computed immediately, and this value is then used in the expression that follows the RETURN statement
Here’s a simple example to illustrate:
Customer% =
VAR SalesAmount = SUMX(Sales, Sales[Quantity] * Sales[Net Price])
VAR NumCustomers = DISTINCTCOUNT(Sales[CustomerKey])
RETURN DIVIDE(SalesAmount, NumCustomers)
In this example:
This approach helps improve readability and performance of your DAX code
Still If you need more information, pls go through follwing documentation
https://learn.microsoft.com/en-us/dax/best-practices/dax-variables
https://www.sqlbi.com/articles/when-are-variables-evaluated-in-dax/
Thanks!
Hi @suparnababu8 . Although your response says variables will be evaluated before the RETURN statement, the SQLBI link you attached stated otherwise, where it states near the end of the article:
"If the variable is defined but not referenced, then it is not evaluated."
The DAX language gets run top to bottom. With how you have written your code, all 4 of your complex code will be run everytime and will be very slow. On top of this, the engine scans your code and tries to only load the tables it needs.
If you were hoping that only code will be run when its needed, the switch statement is designed to do that but it too has its own limits. You would need to put you 'complex calc' inside the switch statement (or any IF statement) to achieve the same effect of only running the code when its needed. There will be occasions where a switch statement will be too big or complex and the optimiser will break and everything will load, giving you headaches.
You can get around this by using Calculation groups to take the place of the switch statement.
As an addition to my reply. Variables are best used when you want to calculate something up front and store the result, to be reused over and over again later in the measure. I often also use variables for things that I will need to change when i copy and paste the code into a new measure, to reuse the method.