USO DE VARIABLES EN POWER BI DAX. Guía y Consejos Avanzados para utilizarlas correctamente.

Understanding Variables in DAX

Introduction to DAX Variables

• The speaker introduces the topic of using variables in DAX, emphasizing its importance and relevance based on previous videos.

• Basic syntax for declaring variables is explained, starting with the keyword var. For example, variable A is set to 8 and variable B to 10.

• The use of the return statement is highlighted as essential for returning values from expressions.

Advanced Variable Usage

• An example illustrates creating a measure that returns a value (e.g., 100), followed by another variable representing orders (e.g., 20).

• A division operation between these two variables results in a new value (5), demonstrating how to perform calculations with variables.

Types of Variables

• Two fundamental principles regarding DAX variables are introduced:

• Always start with var and end with return.

• Variables can be either single values (numbers, measures, text strings) or tables.

Key Principles for Using Variables

• The speaker emphasizes that while you can incorporate columns into variables, it must be within a row context.

• Five critical tips for writing DAX variables are promised; the second tip about treating all variables as constants is particularly emphasized.

Practical Application of Variables

• The first piece of advice states that using variables helps write clearer and more manageable DAX expressions.

• An example scenario involves reversing names from a full name column by utilizing text functions similar to those in Excel.

Improving Code Readability

• The speaker discusses how complex expressions can become difficult to read and debug without using variables effectively.

• Commenting out existing code allows for gradual development using variables, enhancing clarity during coding.

Step-by-Step Variable Declaration

• The process begins by defining a variable called "position" which finds the location of a comma in the full name string.

• By validating intermediate values through return statements, developers can ensure accuracy at each step when writing their code.

Extracting Name Components

• Another variable captures the length of the text string accurately after validation.

• A new variable extracts just the first name from the full name based on previously defined positions.

Understanding Variable Usage in Calculations

Concatenating Names

• The speaker discusses creating a valid last name by concatenating the first name and last name with a space in between, emphasizing the order of operations to ensure correct results.

Efficient Calculation of Margins

• An example is provided where the speaker calculates percentage margin using variables for amount and cost, highlighting efficiency in calculations without needing to repeat expressions.

Advantages of Using Variables

• The use of variables allows for more optimal code as it prevents multiple evaluations of the same expression. This leads to improved performance and readability.

Best Practices for Returning Values

• It is recommended to always return a variable rather than a direct calculation. This approach facilitates easier debugging and testing at different stages of development.

Constants in Variables

• Once created, variables act as constants within their defined context. The speaker warns against modifying filter contexts after variable creation, which can lead to incorrect calculations.

Common Pitfalls with Variables

Misuse of Calculate Function

• A common mistake is using the CALCULATE function with variables instead of measures. This can result in unexpected outcomes since variables do not evaluate context like measures do.

Filtering Tables with Variables

Understanding Table Variables and Filters in Power BI

Creating and Using Table Variables

• A table variable is created to store filtered data, specifically for products that are expensive. The intention is to create a new variable for green products based on this filter.

• If the color filter is applied incorrectly, it will not yield the expected results; instead, it will return the original table of expensive products since it cannot modify its existing context.

• The value assigned to the table does not affect the original table due to its lack of filter context.

Importance of Data Lineage

• When using a table variable, it retains the lineage of data from its source table. This concept has been previously assumed and utilized in earlier cases.

• An example involves creating a measure for sales amounts from top clients who have spent over 500. A variable named "best clients" filters these customers based on their purchase amounts.

Calculating Sales Amounts

• The result obtained from filtering best clients will show a lower sales amount than before because it only includes those who spent more than 500.

• The "best clients" variable creates a new table derived from the client dimension, which affects calculations when used in measures like calculate.

Advanced Measures with Large Orders

• A new measure called "large order amounts at list price" is introduced to calculate sales for orders exceeding four units.

• Filtering records from the fact table where units sold are greater than four allows for focused analysis on larger transactions.

Iteration Over Filtered Tables

• The calculation uses SUMX over the filtered large orders, multiplying sold units by related unit prices to derive total sales at list price.

• Utilizing RELATED function enables access to linked tables while iterating through rows in large orders, maintaining data lineage throughout calculations.

Evaluating Contextual Filters

• Despite being constants, variables maintain their data lineage and can reference original tables within calculations effectively.

• The evaluation process begins with understanding how filtered versions of fact tables operate without altering their inherent relationships or contexts.

Understanding Context Transition in Data Analysis

Key Insights on Accumulated Amount Calculation

• The accumulated amount is calculated as the sum of all values, resulting in 1247, which matches the value on the card.

• A subscriber raised a question about an expression for accumulated amounts that returned identical results across all matrix values due to context transition issues.

• The problem arises from context transition affecting measures, leading to uniform outputs. To resolve this, one should use variables effectively.

• By defining a variable for maximum date and returning its result, accurate accumulated amounts can be achieved without repetitive calculations.

Exploring Filter Context and Its Impact

• The discussion involves a model with fact tables and calendar dimension tables to analyze why the accumulated amount formula yields identical results.

• Filtering context is crucial; for instance, filtering records by March 2018 impacts how data is evaluated within the model.

• The ALL function ignores filters and returns the entire calendar table, which is essential for establishing a new filter context during calculations.

• The FILTER function evaluates expressions row by row; if conditions are met (e.g., dates), it returns corresponding values while modifying filter contexts dynamically.

Addressing Context Transition Challenges

• It's important to remember that when using measures within CALCULATE, they are affected by row context transitions created by functions like FILTER.

• This transition modifies filter contexts such that evaluations may yield unexpected results (e.g., January 1st being treated as equal to itself).

• Each record undergoes evaluation against modified contexts; thus, all records return similar outcomes unless properly filtered.

Implementing Variable Solutions

• To address these challenges, creating a variable for maximum date captures constant values rather than dynamic ones influenced by changing contexts.

• With established variables, evaluating formulas becomes more straightforward since they no longer fluctuate based on broader model changes but remain fixed at specific points (e.g., March 26th).

• This approach allows each record's evaluation against a consistent maximum date rather than recalculating it dynamically through transitions.

Finalizing Accurate Calculations

• Evaluating whether each date meets criteria against fixed variables ensures varied outputs instead of uniformity across records due to previous filter misapplications.

• Ultimately, this method refines filtering processes so only relevant records impact calculations—leading to accurate sums reflective of actual data distributions.

Accumulated Value Calculation

Understanding the Accumulated Amount

• The speaker discusses obtaining an accumulated value of 2782, which is the desired amount up to a specific date.

Advanced Variable Nesting Techniques

• An advanced tip is introduced regarding variable nesting, where variables can be declared within other variables for more complex calculations. This technique will be useful in future applications.

Executing Sums with Nested Variables

• When executing sums in a data table, the speaker demonstrates how to create multiple variables (e.g., units and price) and return their product. This showcases the flexibility of variable usage in calculations.

Complex Expressions and Their Usability

• The speaker emphasizes that while creating complex expressions may seem excessive, it is possible to use them effectively as they yield numerical values that can be manipulated further (e.g., division).

Future Applications of Variable Nesting