ALL functions (DAX) - DAX (2023)

  • Article
  • 7 minutes to read

Returns all rows in a table or all values ​​in a column, ignoring any filters applied. This function is useful for clearing filters and making calculations for all rows in a table.

Syntax

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

Parameter

FinishedDefinition
MesaThe table for which you want to clear the filters.
pillarThe column for which you want to clear the filters.

The argument to the ALL function must be a reference to a base table or a reference to a base column. You cannot use table expressions or column expressions with the TODO function.

return value

The table or column with removed filters.

observations

  • This function is not used by itself, but rather serves as an intermediate function that can be used to change the result set on which another calculation is being performed.

  • The normal behavior of DAX expressions containing the ALL() function is that any filters applied are ignored. However, there are some scenarios where this is not the caseexist automatically, a DAX technology that optimizes filtering to reduce the amount of processing required for certain DAX queries. An example where autoexistence and ALL() gives unexpected results is when you are filtering on two or more columns in the same table (e.g. when using slicers) and there is a measure in the same table that is ALL () used. In this case it is automatically present.to joinseveral filters into one and only filters existing value combinations. Because of this combination, the measure is calculated in addition to existing combinations of values, and the result is based on the filtered values ​​and not on all values ​​as expected. For more information on automatic existence and how it affects calculations, see Microsoft MVP Alberto FerrariDescription of the automatic DAX existencearticle aboutsql.bi.com.

  • The following table describes how you can use the ALL and ALLEXCEPT functions in different scenarios.

    function and usedescription
    AT()Remove all filters everywhere. ALL() can only be used to clear filters, not to return a table.
    ALL(table)Removes all filters from the specified table. In fact, ALL(Table) returns all values ​​in the table and removes from the context any filters that would otherwise have been applied. This feature is useful when you are working with many levels of grouping and want to create a calculation that compares an added value to the total value. The first example demonstrates this scenario.
    ALL(column[, column[, …]])Removes all filters from the specified columns in the table; all other filters for other columns of the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove context filters for one or more specific columns and keep all other context filters. The second and third examples demonstrate this scenario.
    ALLEXCEPT(tabla,column1 [,column2]...)Removes all context filters from the table except those that apply to the specified columns. This is a convenient shortcut for situations where you want to remove filters for many but not all columns in a table.
  • This function is not supported in DirectQuery mode when used in calculated columns or RLS (Row Level Security) rules.

example 1

Calculate the ratio of category sales to total sales

Suppose you want to find the sales amount for the current cell in your pivot table divided by the total sales of all resellers. To ensure that the denominator is the same no matter how the PivotTable user filters or groups the data, define a formula that uses ALL to create the correct grand total.

The following table shows the results of a new measurement,List of sales from all resellers, is created using the formula shown in the code section. To see how this works, add the "CalendarYear" fieldrow labelsthe pivot table and add the ProductCategoryName fieldColumn LabelsArea. Then draw the measureList of sales from all resellers, hinValuesPivot table space. To display results as percentages, use Excel's formatting features to apply a percentage number format to the cells that contain the measure.

row labelsaccesoriesCyclesclothingcomponentsTotal
20050,02%9,10%0,04%0,75 %9,91%
20060,11 %24,71%0,60%4,48%29,90%
20070,36%31,71%1,07%6,79%39,93%
20080,20%16,95%0,48%2,63%20,26%
Total0,70%82,47%2,18%14,65%100,00%

Formula

= SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

The formula is structured as follows:

  1. the counter,SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values ​​in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable, with context filters applied to CalendarYear and ProductCategoryName.

  2. For the denominator, start by specifying a table, ResellerSales_USD, and use the ALL function to remove all context filters from the table.

  3. Then use the SUMX function to sum the values ​​in the ResellerSales_USD[SalesAmount_USD] column. In other words, it gets the sum of ResellerSales_USD[SalesAmount_USD] for all reseller sales.

example 2

Calculate the ratio of product sales to total sales up to the current year

Suppose you want to create a table that shows the percentage of sales versus years for each product category (ProductCategoryName). To get each year's percentage for each ProductCategoryName value, you must divide the sum of sales for that particular year and product category by the sum of sales for the same product category for all years. In other words, you want to keep the filter on ProductCategoryName but remove the filter on the year when calculating the percentage denominator.

The following table shows the results of a new measurement,year of sale of the reseller, is created using the formula shown in the code section. To see how this works, add the "CalendarYear" fieldrow labelsfrom a PivotTable and add the ProductCategoryName field to theColumn LabelsArea. To display results as percentages, use Excel's formatting features to apply a percentage number format to the cells that contain the measure.year of sale of the reseller.

row labelsaccesoriesCyclesclothingcomponentsTotal
20053,48%11,03%1,91%5,12%9,91%
200616,21%29,96%27,29%30,59%29,90%
200751,62%38,45%48,86%46,36%39,93%
200828,69%20,56%21,95%17,92%20,26%
Total100,00%100,00%100,00%100,00%100,00%

Formula

= SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), ALL(DateTime[CalendarYear]))

The formula is structured as follows:

  1. the counter,SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values ​​in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable, with context filters applied to the CalendarYear and ProductCategoryName columns.

  2. For the denominator, the existing filter on CalendarYear is removed using the TODO (Column) function. This calculates the sum of the remaining rows in the ResellerSales_USD table after applying the existing column label context filters. The net effect is that for the denominator, the sum is calculated over the selected ProductCategoryName (the implicit context filter) and for all values ​​in Year.

Example 3

Calculate the contribution of the product categories to the total sales per year

Suppose you want to create a table that shows the percentage of sales for each product category year by year. To get the percentage of each product category in a given year, you need to calculate the sum of the sales of that particular product category (ProductCategoryName) in year n and then divide the resulting value by the sum of the sales of the entire year n. product categories. In other words, you want to keep the filter on the year but remove the filter on ProductCategoryName when calculating the percentage denominator.

The following table shows the results of a new measurement,Name of the reseller sales category, is created using the formula shown in the code section. To see how this works, add the "CalendarYear" fieldrow labelsthe pivot table and add the ProductCategoryName fieldColumn LabelsArea. Then add the new measureValuesPivot table space. To display the results as percentages, use Excel's formatting features to apply a percentage number format to the cells that contain the new measure.Name of the reseller sales category.

row labelsaccesoriesCyclesclothingcomponentsTotal
20050,25%91,76%0,42%7,57%100,00%
20060,38%82,64%1,99%14,99%100,00%
20070,90%79,42%2,67%17,01%100,00%
20080,99 %83,69%2,37%12,96%100,00%
Total0,70%82,47%2,18%14,65%100,00%

Formula

= SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/CALCULATE( SUM(ResellerSales_USD[SalesAmount_USD]), ALL(ProductCategory[ProductCategoryName]))

The formula is structured as follows:

  1. the counter,SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values ​​in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable with context filters applied to the CalendarYear and ProductCategoryName fields.

  2. For the denominator, use the TODO(Column) function to remove the filter on ProductCategoryName and calculate the sum over the remaining rows in the ResellerSales_USD table after applying any existing context filters from the row labels. The net effect is that the denominator is summed over the selected year (the implicit context filter) and over all ProductCategoryName values.

See also

filter functions
All functions
ALL EXCEPT function
FILTER function

Top Articles
Latest Posts
Article information

Author: Errol Quitzon

Last Updated: 02/02/2023

Views: 5750

Rating: 4.9 / 5 (79 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Errol Quitzon

Birthday: 1993-04-02

Address: 70604 Haley Lane, Port Weldonside, TN 99233-0942

Phone: +9665282866296

Job: Product Retail Agent

Hobby: Computer programming, Horseback riding, Hooping, Dance, Ice skating, Backpacking, Rafting

Introduction: My name is Errol Quitzon, I am a fair, cute, fancy, clean, attractive, sparkling, kind person who loves writing and wants to share my knowledge and understanding with you.