Data Analysis Expressions (DAX) along with CALCULATE functions in Power BI and Level of Detail (LOD) expressions in Tableau are two key components that enable users to perform complex calculations and gain deeper insights into their data.

I have always struggled to understand the DAX and CALCULATE function in Power BI. This blog explores comparisons between Tableau LODs and Power BI DAX expressions for various filter modifier functions, including ALL, ALLEXCEPT and KEEPFILTERS.

ALL:

Before applying ALL('Sales'[Product]), let's say we have filters applied to the 'Product' column, restricting the data to only "ProductA" and "ProductB".

Power BI DAX:

Total Sales = CALCULATE(SUM('Sales'[Sales]), ALL('Sales'[Product]))

The ALL function in Power BI removes filters from the specified column, allowing calculations to be performed without considering any filters applied to that column.

After applying ALL('Sales'[Product]), the filters on the 'Product' column are removed, allowing calculations to be performed considering all products. Therefore, the result would be the total sales across all products, regardless of the filters applied initially.

Tableau LODs:

{ FIXED [Product] : SUM([Sales]) }

In Tableau, you can achieve a similar result by fixing the LOD expression at the level of the 'Product' dimension, effectively ignoring any filters applied to the 'Product' column.

ALLEXCEPT:

Before applying ALLEXCEPT('Sales', 'Sales'[Product]), let's say we have filters applied to both the 'Product' and 'Region' columns.

Power BI DAX:

Total Sales Except ProductA = CALCULATE(SUM('Sales'[Sales]), ALLEXCEPT('Sales', 'Sales'[Product]))

The ALLEXCEPT function in Power BI removes all filters from the specified table except for the ones applied to the specified columns.

After applying ALLEXCEPT('Sales', 'Sales'[Product]), all filters are removed from the 'Sales' table except for the 'Product' column. Therefore, the result would be the total sales for each product, considering filters applied only to the 'Product' column and ignoring filters applied to other columns such as 'Region'.

Tableau LODs:

{ EXCLUDE [Product] : SUM([Sales]) }

In Tableau, the EXCLUDE LOD expression excludes the specified dimension ('Product' in this case) from the filter context, ensuring that calculations are performed without considering filters applied to that dimension.

KEEPFILTERS:

Before applying KEEPFILTERS('Sales'[Region] = "North"), let's say we have filters applied to other columns, such as 'Product' and 'Date'.

Power BI DAX:

Total Sales = CALCULATE(SUM('Sales'[Sales]), KEEPFILTERS('Sales'[Region] = "North"))

The KEEPFILTERS function in Power BI applies additional filters to the calculation context, keeping the existing filters intact.

After applying KEEPFILTERS('Sales'[Region] = "North"), the additional filter is applied to the calculation context, keeping the existing filters intact. Therefore, the result would be the total sales for the "North" region, considering other filters applied to the dataset.

Tableau LODs:

{ INCLUDE [Region] : SUM([Sales]) }

In Tableau, you can achieve similar functionality by using an INCLUDE LOD expression, which includes the specified dimension ('Region' in this case) in the filter context, ensuring that calculations are performed considering filters applied to that dimension.