TRANSICION DE CONTEXTO EN DAX. Domina el concepto mas complejo de Power BI.

Understanding Context Transition in DAX

Introduction to Context Transition

• The video introduces the concept of context transition in DAX, which is considered one of the most complex theoretical pillars of the language.

• It emphasizes that understanding row context and filter context is crucial for grasping context transition, which will enable the creation of more sophisticated indicators.

Definition and Implications

• A formal definition states that context transition allows CALCULATE and CALCULATETABLE functions to convert a row context into an equivalent filter context during DAX expression evaluation.

• Three key ideas are highlighted:

• Context transition is a property of CALCULATE.

• Row context and filter context are distinct; row context pertains to the current iteration while filter context refers to applied filters.

• Two requirements must be met for a successful transition: being in a row iteration and using the CALCULATE function.

Functions Generating Context Transition

• The only two functions that generate a direct transition of context are CALCULATE and CALCULATETABLE.

• Many time intelligence functions imply a transition due to their internal workings resembling those of CALCULATE, even if they do not explicitly create one.

Practical Application with Calculated Columns

• To illustrate, calculated columns are introduced as simple objects in DAX where understanding transitions can be demonstrated effectively.

• An example model with product sales data is presented, aiming to create a calculated column reflecting sales amounts per product.

Issues with Calculated Columns

• When attempting to use a measure formula directly as a calculated column, it returns identical values across all rows due to lack of filter context.

Understanding Context Transition in DAX Calculations

The Role of Filters and Calculated Columns

• When the filter is empty, the model does not apply any filtering. The formula evaluates to the sum of all elements in the "monto" column, resulting in a total value (e.g., 25,600.59).

• Adding CALCULATE changes the context; it allows for returning individual sales amounts for each product instead of a single aggregated value.

• In calculated columns, only row context exists. However, using CALCULATE creates a transition from row context to filter context by incorporating all elements of that row into the filter context.

• This transition enables accurate calculations per product, demonstrating how CALCULATE modifies evaluation contexts effectively.

Importance of Using CALCULATE

• It's crucial to remember that every measure in DAX implicitly includes a CALCULATE. This understanding helps interpret measures correctly as if they always had an implicit calculation surrounding them.

• For example, creating a calculated column for the first sale date requires understanding that without CALCULATE, it returns identical values across rows. With CALCULATE, it accurately reflects each product's first purchase date.

Filtering Customers Based on Purchase Quantity

• To create a table filtering customers who purchased more than seven units, one must use the FILTER function effectively within a calculated table.

• Initially executing this formula may return no records because it doesn't apply any filters due to lack of proper context.

• The function iterates through each customer but fails to filter correctly since it sums up total units across all data rather than per customer due to absence of filter context.

Correcting Filter Context with CALCULATE

• Introducing CALCULATE around expressions allows for correct filtering based on conditions set within the function. It ensures that evaluations consider only relevant data points.

• By applying CALCULATE, now when evaluating whether unit sums exceed seven, it accurately reflects individual customer purchases rather than total units across all customers.

• This adjustment leads to meaningful results where calculations reflect true customer behavior rather than aggregate totals.

Conclusion: Mastering DAX Context Transitions

• Understanding how DAX handles contexts—row vs. filter—is essential for effective data modeling and analysis.

Understanding DAX Context Transition

Introduction to Filter Context

• The initial formula demonstrates how filter context operates within a dataset, where each row is evaluated against specific conditions. Only rows meeting the criteria (e.g., sum of economy greater than 7) are returned.

Calculated Tables and Their Use

• The speaker discusses the drawbacks of creating calculated tables in DAX for educational purposes, emphasizing that while they can be useful, they should generally be avoided in practice.

Utilizing CALCULATE with Filters

• A powerful combination of CALCULATE and filters is introduced. This allows users to create sophisticated indicators by applying filters from previously created tables to original measures like sales amounts.

Implicit CALCULATE in Measures

• It’s noted that every measure in DAX has an implicit CALCULATE. Understanding this concept helps developers recognize that results remain consistent even when explicit calculations are omitted.

Distinct Count and Total Calculation

• The example illustrates how distinct counts behave differently at total levels compared to monthly breakdowns due to potential customer duplication across months.

Creating Revised Measures

Adjusting for Total Counts

• The speaker aims to create a new measure called "number of clients revised" using iterators and the VALUES function, which will allow totals to reflect accurate sums rather than distinct counts.

Data Model Considerations

• There’s a note on the importance of properly structuring data models, particularly calendar dimensions, which should encompass all relevant dates without unnecessary limitations.

Understanding Filter Context at Total Level

• At the total level with no active filters, the model evaluates all data points. This leads to different outcomes between original and revised measures due to how filtering impacts calculations.

Iterators and FILTER Context

Functionality of VALUES in Iteration

• The use of VALUES returns a table with unique values from specified columns. This forms the basis for iterating through each row during calculations.

Transitioning Filter Context

• As iterations occur, filter context changes dynamically based on current row evaluations. Each cell's value reflects filtered results according to its respective context rather than overall totals.

Understanding Contextual Filters and Aggregation in Data Models

Steps in Data Aggregation

• The next step involves executing aggregation, specifically summing all elements of a generated table, resulting in a total of 17. This value corresponds to the original measure for the month of October 2019.

• In the second step, a filter context is applied, leading to a filtered model. Evaluating the original measure returns three distinct customer values as expected.

• The function VALUIS considers filter context; it generates a virtual table with unique values based on available data for October 2019, iterating through one row.

• Although an additional filter context is created for October 2019, it remains redundant since the existing filter already encompasses this date. Thus, the result stays at three distinct customers.

• A new example illustrates sales by year; identifying peak sales days reveals that 2018's best day yielded nearly $24,000 while 2019's best was $1,524.

Creating Measures for Sales Analysis

• A new measure called "Best Day Sales" is introduced using MAXX and VALUES, which will directly indicate the highest sales figure from defined values.

• The original sales measure contrasts with the newly created "Best Day Sales" measure. Filtering by year 2019 ensures only relevant records are evaluated.

• Evaluating both measures shows discrepancies: while the original sums up to $1.51 (not accurate), the new measure correctly identifies maximum sales figures through its iterative process.

• The formula utilizes MAXX, which operates similarly to SAMX, returning a table of unique dates filtered by context and executing iterations accordingly.

• Finally, aggregating results leads to identifying maximum sales from calculated columns. This technique demonstrates how integrating functions like MAXX and VALUIS can reduce complexity in expressions effectively.

Upcoming Topics