CONTEXTO DE FILTRO. Domina el concepto teórico MAS IMPORTANTE de DAX.

What is Filter Context?

Introduction to Filter Context

• Julio introduces the topic of filter context, emphasizing its importance in understanding data evaluation within DAX expressions.

• He encourages viewers to watch previous videos, particularly on row context, as they will build upon those concepts.

Definition and Importance

• The formal definition of filter context is presented: it refers to the set of filters applied during the evaluation of a data expression.

• A filter is described as a collection of values that restrict records in a table; for example, filtering a category column limits the displayed data.

Expression of Filters

• Filters can be expressed in two ways: extensive (column name and value) or tabular format. The latter will be emphasized moving forward.

• It’s crucial to understand that all filters must ultimately be expressed as tables for effective manipulation in DAX.

Existence and Impact of Filter Context

• Julio explains that filter context always exists; even without explicit filters, an empty filter context still applies.

• The discussion shifts to which DAX objects are affected by filter context—primarily measures and tables.

Creating Filter Context with DAX Functions

Functions That Create Filter Context

• Only two DAX functions create filter contexts: CALCULATE for measures and CALCULATETABLE for tables.

• Julio clarifies that while the FILTER function can modify tables, it does not generate a new filter context like CALCULATE.

Example in Power BI

• An example using Power BI illustrates how adding dimensions (like product categories) affects measure calculations independently based on their specific contexts.

Understanding Measure Calculations

Independent Calculation Based on Context

• In Power BI, when no filters are applied, the total sales amount reflects the entire dataset.

• Adding product categories creates distinct values for each category's sales total due to different contexts being applied.

Further Exploration of Data Relationships

• Each element in reports calculates independently under specific restrictions defined by their respective contexts (e.g., category and gender).

Deep Dive into Matrix Calculations

Analyzing Specific Values in Matrices

• Julio discusses how each part of a matrix report has its own unique calculation based on its defined context.

Conclusion on Understanding Filter Context

Filtering Context in Data Models

Understanding Filtering Context

• The filtering context is established by applying filters based on the gender (male) from the customer dimension and the category (Games and Toys) from the product matrix.

• When filtering by gender, all records related to male customers are selected, followed by filtering products that fall under the Games category due to existing relationships between tables.

• After applying these filters, a refined model emerges with specific records for five clients and four products, demonstrating how filtering operates independently of formula evaluation.

Applying Aggregation Functions

• The measure applied involves summing values from the sales amount column, resulting in a total of 440. This highlights that aggregation functions are evaluated after establishing the filtered model.

• Different aggregation functions (e.g., Max, Min) can be used interchangeably; however, they will yield different results while maintaining the same underlying algorithmic process.

Advanced Aggregation Techniques

• A unique aggregation function called COUNTROWS allows counting rows within a table as input rather than just a single column. This function returns row counts based on previously filtered contexts.

• In this case, with four rows in the filtered model for male customers in Games, COUNTROWS would return 4. This demonstrates how measures adapt to context changes effectively.

Exploring Filter Context Variations

• The report's filter context can change dynamically through various interactions such as clicking on visual elements or slicers like year segments or marital status.

• Multiple filters contribute collectively to determine values displayed; modifying any part of this context alters outcomes significantly.

Creating Calculated Tables

• A new calculated table named "Product" is created outside of report-level filters; thus it starts with an empty filter context.

• Using functions like FILTER modifies table data but does not inherently alter filter contexts unless explicitly defined through certain calculations.

Utilizing Values Function

• The VALUES function generates distinct values from a specified column (e.g., brand), creating a new table without any active filter context at report level.

Understanding Context and Calculations in Power BI

Introduction to Measures and Filters

• The discussion begins with the impact of filter context on measures in Power BI, highlighting that calculated tables are affected differently than virtual tables.

• A formula called COUNTROWS is introduced to determine the number of products sold, emphasizing its role in counting rows from a table of values.

Evaluating Measure Values

• The speaker explains how each point in a matrix is calculated independently under different contexts, setting up for an example evaluation.

• Step 1 involves identifying the filter context (e.g., gender and category), which influences how data is filtered within the model.

• In Step 2, the model filters dimensions before applying formulas; if only COUNTROWS were used on the fact table, it would yield a count of four rows.

Understanding Virtual Tables

• The use of VALUES creates a virtual table based on distinct product values rather than all entries, leading to three unique counts instead of four due to repetitions.

• This process illustrates how COUNTROWS counts rows in this virtual table, resulting in an accurate representation of distinct products.

Analyzing Additional Examples

• Another example evaluates a value (10), noting that no filters affect it at this stage—demonstrating an empty filter context.

• The model remains unfiltered for this calculation; thus, using VALUES generates a virtual table with distinct records rather than all available entries.

Distinct Count vs. COUNTROWS + VALUES

• A comparison is made between using COUNTROWS combined with VALUES versus using the existing function DISTINCTCOUNT, indicating they serve similar purposes but have different applications.

• Emphasis is placed on understanding how VALUES can facilitate advanced calculations and complex indicators within Power BI.

Creating Advanced Indicators

• The speaker introduces creating a measure to count records with more than two units sold by utilizing both filter context and row context concepts together.

• A new measure titled "number of records greater than two units" is proposed as an educational tool to demonstrate these concepts effectively.

Filtering Contextual Analysis

• To calculate specific values (like 2), step one identifies active filters—in this case, gender as female—with no category filters applied.

• After filtering the model based on these criteria, the formula applies; here, it uses FILTER, which generates row context iteratively over filtered data.

Iteration Through Filtered Data

• During iteration via FILTER, only records meeting conditions (units greater than two) remain; thus allowing for precise counting through subsequent application of COUNTROWS.

Understanding Filter Contexts in DAX

Evaluating Filter Context and Row Context

• The discussion begins with the evaluation of filter context followed by row context, emphasizing their importance depending on the function used in a formula.

• It is noted that when a specific column (e.g., "monto") is not available, an iteration function can be utilized to derive sales amounts without creating calculated columns.

• The speaker demonstrates how to change a measure into an iterative function form, showing how values are returned within a matrix based on evaluated expressions.

Steps for Calculating Values

• Step one involves understanding the filter context, which includes dimensions like gender and category. This filtered model is crucial for applying measures correctly.

• The second step applies the measure's formula using an iteration function (e.g., SUMX), which evaluates under the established row context to return accurate results.

Applying Functions for Sales Indicators

• A new indicator request arises: calculating sales amounts from customers in Asia. Understanding previous concepts allows for this calculation using filtering and relational functions.

• The speaker suggests executing a SUMX over a filtered table where customer continent equals Asia, summing units multiplied by unit price as an initial approach.

Improving Calculation Methods

• While the initial method works, it’s noted that there exists a more efficient way to obtain values through the CALCULATE function, which modifies existing filter contexts effectively.

• By replacing complex formulas with CALCULATE, one can simplify expressions while still achieving accurate results related to Asian customers.

Understanding CALCULATE Functionality

• The original measure ("monto de ventas") is compared against a new measure created using CALCULATE. This highlights differences in values across categories due to additional filters applied.

• The operation of CALCULATE introduces an additional filter context (continent), demonstrating its significance as potentially the sixth method of incorporating filter contexts into DAX expressions.

Final Insights on Filtering Context

• With CALCULATE, users can add or remove filters from their calculations easily. This flexibility enhances data analysis capabilities significantly.

Understanding Context Transition in DAX

Introduction to Context Transition

• The upcoming topic focuses on the important theoretical aspect of DAX, specifically context transition.

• If viewers have grasped the concepts of row context and filter context, they will find it easier to learn about context transition.