ALL, ALLEXCEPT Y ALLSELECTED en DAX. Las Principales Funciones para remover filtro en Power BI.

Understanding the Functions ALL and ALLSELECTED

Introduction to Functions

• The video introduces the functions ALL and ALLSELECTED, highlighting their utility in ignoring or removing filters from expressions.

• These functions have a dual nature, functioning both as table functions and as modifiers within calculations, which can complicate understanding.

Contextual Knowledge Required

• A solid grasp of concepts such as row context, filter context, and context transition is essential for effectively using these functions.

• The tutorial will cover basic syntax, usage in calculated tables, simple measures, and advanced applications like combining ALL with other functions.

Syntax and Functionality of ALL

• The generic syntax of the ALL function requires a mandatory argument (a table or column), with optional additional columns.

• As a table function, ALL returns all rows from a specified table or distinct values from a column while ignoring any applied filters.

Differences Between ALL and VALUES

• Understanding the distinction between ignoring a filter versus removing it is crucial; this will be illustrated through examples later on.

• When creating a calculated table using ALL, it returns distinct values similar to VALUES, but they behave differently under DAX expressions due to filter considerations.

Advanced Usage of ALL

• The ALL function allows referencing multiple columns simultaneously, generating combinations across those columns.

• It can also create an identical copy of an entire dimension that ignores any external filters when used in DAX expressions.

Variants: ALL vs. ALLSELECTED

• The variant ALLEXCEPT allows returning all data except for specified columns by defining exceptions.

• In contrast to ALL, the function ALLSELECTED ignores internal filters but considers external ones when used in DAX expressions.

Practical Application Example

• An example illustrates creating a measure for percentage participation of subcategories over total sales using pre-built measures versus explicit measures for more complex analysis.

Understanding Sales Participation Metrics

Calculating Sales Participation

• The speaker discusses the calculation of participation for the subcategory "botsgames," using a formula that divides specific values (35 over 700 and then over 1833) to derive insights.

• A new measure called "monto de ventas all" is introduced, utilizing an iteration function to enhance data analysis.

• The operation involves generating a row operation on the "all" version of the fact table, allowing for consistent value returns across records in a matrix.

Creating Measures and Understanding Context

• The speaker successfully creates a percentage participation measure by defining it as the division between sales amount and a newly created measure.

• A matrix is constructed with two measures: total sales and participation, illustrating how these metrics interact within the model.

Filter Context and Calculation Process

• The context of filters is emphasized; understanding how filter contexts affect calculations is crucial for accurate data representation.

• The "all" function is explained as one that generates a complete table while ignoring any applied filters, which allows for comprehensive data analysis.

Aggregation and Total Values

• An aggregation process sums elements from what is referred to as a "phantom column," leading to consistent total values (1883).

• This total remains unaffected by various filters applied in different parts of the matrix, ensuring reliable comparisons across categories.

Adjusting Measures Based on Filters

• When selecting specific colors (e.g., White and Yellow), original measures are affected by color filters while maintaining overall totals at 1883.

• To ensure participation equals 100%, adjustments must be made so that measures respond appropriately to color filters without being influenced by category filters.

Implementing ALLSELECTED Functionality

• By employing the ALLSELECTED function instead of traditional methods, expected values can be achieved even when multiple filters are applied simultaneously.

• Modifying measures with ALLSELECTED allows for dynamic responses to external filters like color while ignoring internal category constraints.

Finalizing Measure Adjustments

• Steps are reiterated: establishing filter context includes both subcategory and new color selections.

Understanding Context and Calculation in Data Analysis

Evaluating Arguments and Aggregation

• The process begins with evaluating the second argument in a row context, calculating values, and ultimately assessing the aggregation, which is the sum of these values. The expected result is 710.

• It's noted that understanding the tabular form of functions is beneficial for applying them in special cases, particularly with advanced filter variants. However, this isn't the most common usage.

Modifying Calculations Efficiently

• Instead of using iteration, a more efficient approach involves using calculate to modify the original measure directly. This method yields identical results while enhancing code efficiency.

• Although both methods produce the same outcome, their operational differences are crucial to understand for effective data manipulation.

Filter Context and Its Implications

• A model modification example illustrates replacing interaction with calculation. The first step involves establishing a filter context based on categories like "Download games."

• The all function removes any filters affecting the table fragments rather than ignoring them. This subtle distinction becomes significant when dealing with more sophisticated indicators later on.

Advanced Filtering Techniques

• Unlike previous examples where filters were ignored, here they are removed entirely from consideration during calculations. This leads to evaluating formulas based solely on column sums.

• When applying color filters (e.g., White and Yellow), similar results can be achieved by switching from all to all selected, maintaining a 100% sum as desired.

Understanding Internal vs External Filters

• Using all selected modifies filter contexts by removing internal filters while considering external ones. This changes how models are filtered based on specific criteria like color.

• In this scenario, only color filters are applied while subcategory filters are disregarded during evaluation, leading to accurate summation results (e.g., 710).

Creating Measures for Accurate Percentages

• To ensure columns sum up to 100%, a new measure called "total amount per year" is created using sales amounts calculated through specific measures.

• By dividing amounts over newly defined variables within calculations, it ensures that each column accurately reflects its percentage contribution towards 100%.

Key Takeaways on Calculation Modifiers

• Understanding that modifiers like all do not return tables but act as evaluative tools is essential for proper data analysis techniques.

Understanding Filter Context in Data Analysis

Evaluating Filter Context and Calculations

• The discussion begins with calculating a value of 22.8, emphasizing the importance of filter context derived from columns and rows, specifically for female gender data from the year 2018.

• It is explained that the initial calculation evaluates its arguments by removing any filters affecting the fact table, thus ignoring gender and year filters.

• The concept of using VALUES is introduced; it returns a table with distinct values from a column while considering the current filter context (year 2018).

• After applying the new filter for 2018, calculations are performed to sum values, resulting in totals like 903 and 904 for different variables.

• The result variable represents a division of original event amounts by previously defined totals, yielding an expected percentage outcome of 22.8%.

Alternative Methods for Filter Restoration

• An alternative method using ALL EXCEPT is mentioned but deferred for more advanced discussions; ALL is suggested as a better way to restore filters at this level.

• A practical application of ALLEXCEPT in calculated columns is introduced, aiming to determine how many records each client has.

Creating Calculated Columns

• The speaker plans to create a new column that counts total records per client based on existing data in the fact table.

• Using COUNTROWS, it’s noted that without filter context (as it's a calculated column), all clients would return a count of 21 records.

• Transitioning context through CALCULATE will yield one record per case due to row-level execution; however, adjustments are needed to achieve accurate results.

Implementing Filters Effectively

• By utilizing filtering techniques that exclude all but the client-specific filters, accurate counts are achieved across various clients (e.g., Client 6 shows three records).

• The explanation continues on how calculated columns generate row-by-row execution while maintaining proper filter contexts through transitions initiated by functions like CALCULATE.

Understanding Row Context and Filtering Techniques

• It’s clarified that when using CALCULATE, every row adds its own context which can be manipulated effectively with functions like ALLEXCEPT.

• This function removes all filters except those relevant to specific columns (like client), allowing precise evaluations based on filtered datasets.

• For example, only records related to Client 6 are considered during calculations leading to accurate results reflecting actual transactions or interactions.

Advanced Applications and Conclusion

• The speaker emphasizes these techniques' utility for complex calculations regarding customer transactions over time or frequency within specified periods.