In this article, we will explore the concept of variables in DAX and provide you with a range of practical examples to demonstrate their usefulness. Variables are useful when you want to store intermediate results or simplify complex formulas by breaking them down into smaller, more manageable parts.
To define a variable in DAX, you use the VAR keyword followed by the variable name, an equal sign (=), and the expression or calculation that the variable represents. To call variable return keyword followed by variable name. Here's the general syntax:
VarTest =
Var Test = "Sample"
Return Test
In below example, we declare a variable named "TotalSales" and assign it the sum of the "Amount" column from the "Sales" table.
Measure2=
VAR TotalSales = SUM(Sales[Amount])
Return TotalSales In this measure, we calculate the total sales by region. The variable "TotalSales" is assigned the sum of the "Amount" column, and then it is referenced within the CALCULATE function to filter sales for the "North" region.
TotalSalesByRegion = VAR TotalSales = SUM(Sales[Amount]) RETURN CALCULATE(TotalSales, Sales[Region] = "North")Now lets take an tricky example to under variables importance and these can make life easy.
Problem Statement: Assume we have passenger fare table, one person can have more than 1 fare record. Our objective is to calculate the average fare for each quartile and quartile should be calculated based on aggregation at person level. Further quartile for person should change dynamically based on filtering.
Solution Overview: To solve this problem, we will follow a step-by-step approach. Here's a detailed explanation of each step:
Step 1: Create a Summary Table We begin by creating a summary table variable that includes unique passenger IDs (PassengerId) and their corresponding total fare values. We can use the SUMMARIZE function to achieve this. The code snippet is :
VAR SummaryTable =
SUMMARIZE(
PassengerDetails,
PassengerDetails[PassengerId],
"TotalFare", SUM(PassengerDetails[Fare]) ) dynamically using DAX code in Power BI. We will determine quartiles based on the fare values in a dataset called PassengerDetails.
Step 2: Calculate Quartile Values
Next, we calculate the quartile values by adding a new column called "Fare" to the QuartileValues table. This column will contain the fare values from the SummaryTable. The code snippet is:
VAR QuartileValues =
ADDCOLUMNS(
SummaryTable,
"Fare", [TotalFare] )Step 3: Calculate Quartile Values
We calculate the quartile values using the PERCENTILEX.INC function. This function takes the QuartileValues table, the column to evaluate ([Fare]), and the quartile value (e.g., 0.25 for the first quartile). We repeat this step for each quartile. Here's an example for the second quartile:
VAR QuartileValue2 =
PERCENTILEX.INC(
QuartileValues, ([Fare]), 0.50 )Step 4: Filter Summary Table
Now, we filter the SummaryTable based on the quartile values calculated in the previous step. We use the FILTER function to include rows where the total fare is greater than the previous quartile value and less than the current quartile value. For example:
VAR FilteredSummaryTable =
FILTER(
SummaryTable,
[TotalFare] > QuartileValue && [TotalFare] < QuartileValue2 )Step 5: Calculate Average of Quartile
Finally, we calculate the average fare for each quartile using the SUMX function. We sum the fare values within the FilteredSummaryTable to obtain the average for the current quartile. Here's the code snippet:
VAR AverageFare =
SUMX(FilteredSummaryTable, [TotalFare])
RETURN
AverageFare
Steps 6. Below is combined query-
VAR SummaryTable =
SUMMARIZE(
PassengerDetails,
PassengerDetails[PassengerId],
"TotalFare", SUM(PassengerDetails[Fare]) )
VAR QuartileValues =
ADDCOLUMNS(
SummaryTable,
"Fare", [TotalFare] )
VAR QuartileValue =
PERCENTILEX.INC(
QuartileValues, ([Fare]), 0.25 )
VAR QuartileValue2 =
PERCENTILEX.INC(
QuartileValues, ([Fare]), 0.50 )
VAR FilteredSummaryTable =
FILTER(
SummaryTable,
[TotalFare] > QuartileValue && [TotalFare] < QuartileValue2 )
VAR AverageFare =
SUMX(FilteredSummaryTable, [TotalFare])
RETURN
AverageFare Things that make Variables powerful are-
Understanding Variables in Power BI DAX: Variables act as containers that hold values, tables, or expressions within a DAX formula. They allow you to store intermediate results, simplify complex calculations, and reuse values throughout your measures, making your formulas more efficient and easier to maintain.
Basic Variable Usage: Let's start with a simple example. Imagine you want to calculate the total sales for a specific product category. Instead of repeating the formula for each measure, you can use a variable to store the product category and refer to it multiple times. This not only reduces the formula's complexity but also improves its performance.
Conditional Logic Made Easy: Variables are incredibly useful when dealing with complex conditional logic. For instance, suppose you want to calculate the average sales only for weekdays. By using a variable to filter out weekends, you can streamline your calculation and enhance readability.
Dynamic Calculations with Variables: One of the key advantages of variables is their ability to enable dynamic calculations. We'll explore scenarios where variables can be used to switch between different calculation modes based on user selections or slicer values. This flexibility empowers you to build interactive and responsive reports.
Iterating Through Data with Variables: Variables are not limited to simple scalar values; they can also hold tables or table expressions. We'll demonstrate how to leverage this capability to iterate through data, apply complex filtering conditions, and perform advanced calculations. From calculating running totals to applying custom aggregations, the possibilities are endless.
Performance Optimization: In addition to enhancing readability, variables can significantly improve the performance of your Power BI reports. We'll discuss optimization techniques, such as using variables to store frequently accessed measures or reducing the number of redundant calculations. By leveraging variables strategically, you can achieve faster report execution and a smoother user experience.
Limitations:
Limited Scope: Variables have a limited scope within the calculation they are declared in and are not accessible outside of that scope. This means you cannot reference a variable in another calculation or reuse it across different measures or calculations.
Lack of Persistence: Variables are temporary and cease to exist once the calculation is completed. They cannot be used to store values or persist data between different queries or refreshes.
Performance Impact: While variables can improve code readability and maintainability, they can sometimes have a slight performance impact, especially if used excessively or in complex calculations.
Overuse and Code Complexity: Excessive usage of variables or complex nesting of variables can lead to code complexity and reduced readability, defeating the purpose of using variables in the first place.
Conclusion: Variables in Power BI DAX unlock a world of possibilities for creating more efficient, flexible, and readable calculations. They empower you to simplify complex logic, enable dynamic calculations, and optimize performance. Armed with the knowledge and examples provided in this blog post, you're now ready to take advantage of variables and elevate your Power BI game. So, go ahead, experiment with variables, and unleash the full potential of your data analyses!
Remember, practice makes perfect, so dive into Power BI and start harnessing the power of variables today. Stay tuned for more exciting content on Power BI and data analytics. Happy analyzing!
No calculate.