All and All Except in Power Bi DAX

In DAX (Data Analysis Expressions), which is used in Power BI, Excel Power Pivot, and SQL Server Analysis Services, ALL and ALLExcept are functions used for manipulating the context of calculations, particularly in measures and calculated columns. These functions are primarily used to remove or modify filters in a DAX expression. Let’s delve into each function in detail.

ALL Function

The ALL function is used to remove filters from one or more columns or tables. It essentially resets the filters on the specified columns or tables, making calculations ignore the current filter context for those columns or tables. This is particularly useful for creating measures like totals, percentages, and averages that are independent of the current filter context.

Syntax

DAX

ALL([<TableNameOrColumnName>])

Parameters

• <TableNameOrColumnName>: The name of the table or column from which you want to remove filters. This can be a single column, multiple columns, or an entire table.

Examples

1. Removing Filters from a Single ColumnDAX

TotalSales := CALCULATE(SUM(Sales[Amount]), ALL(Sales[Region]))

This measure calculates the total sales amount ignoring any filters applied to the Sales[Region] column.

Removing Filters from an Entire Table

DAX

1. TotalSales := CALCULATE(SUM(Sales[Amount]), ALL(Sales)) This measure calculates the total sales amount ignoring any filters applied to the entire Sales table.

ALLExcept Function

The ALLExcept function is used to remove all filters from a table except for the ones explicitly specified. This is useful when you want to keep certain filters while removing others.

Syntax

DAX

ALLExcept(<Table>, <Column>[, <Column>[, ...]])

Parameters

• <Table>: The table from which you want to remove filters.
• <Column>: One or more columns to retain their filters.

Example

1. Retaining Filters on Specific ColumnsDAX
2. TotalSalesByRegion := CALCULATE(SUM(Sales[Amount]), ALLExcept(Sales, Sales[Region])) This measure calculates the total sales amount while keeping any filters on the Sales[Region] column and removing filters from all other columns in the Sales table.

Use Cases and Differences

• ALL is typically used when you want to completely ignore filters on certain columns or an entire table. This is useful for creating measures that need a full data context, such as grand totals or percentages relative to the entire dataset.
• ALLExcept is used when you want to ignore all filters except for specific ones. This is helpful in scenarios where you need to maintain some filtering context (e.g., maintaining a regional filter while ignoring product categories).

Practical Examples

1. Percentage of Total SalesDAX

PercentOfTotalSales := 
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

This measure calculates the percentage of total sales for the current filter context compared to the total sales without any filters.

Sales Contribution by Region Ignoring Product Filters

DAX

1. SalesByRegion := CALCULATE( SUM(Sales[Amount]), ALLExcept(Sales, Sales[Region]) ) This measure calculates sales by region while ignoring any filters on product categories or other columns, focusing solely on regional contributions.

Understanding ALL and ALLExcept functions allows you to create more flexible and powerful measures in DAX by manipulating the filter context to meet specific analytical needs.

give some data to test thisChatGPT

Certainly! Here is a simple dataset to illustrate how you can use ALL and ALLExcept in DAX.

Sample Data

Imagine you have the following Sales table:

Region	Product	Amount
North	A	100
North	B	200
South	A	150
South	B	300
East	A	50
East	B	100
West	A	200
West	B	400

Calculations

Total Sales Ignoring All Filters

Using the ALL function to ignore all filters:

DAX

TotalSales := CALCULATE(SUM(Sales[Amount]), ALL(Sales))

This measure calculates the total sales amount across all regions and products, ignoring any filters applied.

Expected Result:

• TotalSales = 1500 (sum of all amounts in the table)

Total Sales by Region Ignoring Product Filter

Using the ALLExcept function to ignore product filters but keep region filters:

DAX

SalesByRegion := CALCULATE(SUM(Sales[Amount]), ALLExcept(Sales, Sales[Region]))

This measure calculates the total sales amount for each region, ignoring any filters on the Product column.

Expected Results:

• For Region “North”: SalesByRegion = 300 (100 + 200)
• For Region “South”: SalesByRegion = 450 (150 + 300)
• For Region “East”: SalesByRegion = 150 (50 + 100)
• For Region “West”: SalesByRegion = 600 (200 + 400)

Percentage of Total Sales

Using ALL to calculate the percentage of total sales for each row:

DAX

PercentOfTotalSales := 
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

This measure calculates the percentage of total sales for the current filter context compared to the total sales without any filters.

Expected Results (for each row in the Sales table):

• For (North, A): 100 / 1500 = 0.0667 (or 6.67%)
• For (North, B): 200 / 1500 = 0.1333 (or 13.33%)
• For (South, A): 150 / 1500 = 0.10 (or 10%)
• For (South, B): 300 / 1500 = 0.20 (or 20%)
• For (East, A): 50 / 1500 = 0.0333 (or 3.33%)
• For (East, B): 100 / 1500 = 0.0667 (or 6.67%)
• For (West, A): 200 / 1500 = 0.1333 (or 13.33%)
• For (West, B): 400 / 1500 = 0.2667 (or 26.67%)

Applying Filters in a Pivot Table

Let’s assume you create a Pivot Table in Power BI or Excel Power Pivot using the Sales table.

1. TotalSales Measure: Drag the TotalSales measure into the Values area. This will show 1500 for all rows and columns because it ignores all filters.
2. SalesByRegion Measure: Drag the Region column to Rows and the SalesByRegion measure to Values. This will show the sales totals for each region, ignoring product filters.
3. PercentOfTotalSales Measure: Drag the Region column to Rows and Product column to Columns, and then drag the PercentOfTotalSales measure to Values. This will show the percentage contribution of each region and product combination to the total sales.

By setting up these calculations and visualizations, you can test and verify how ALL and ALLExcept work to manipulate filter contexts in your DAX expressions.