Unapply a Filter Using DAX

March 28, 2023 - by Bill Jelen

With SUMIFS, you go through a data set, finding rows that match all of the criteria.

With CALCULATE, you go through a data set, calculating values that match the filters in CALCULATE. BUT...you also have an external outside force that is forcing other filters to be applied. Those filters might be coming from the slicers or even from the row and column labels. When Power Pivot goes about calculating cell F4 in the figure above, it has to respect the weekday=Saturday in the CALCULATE function, but it also has to respect Month=Jan caused by the row label in D4 and Year=2016 caused by the slicer.

More Reading

Calculate() is Like SUMIFS() »

March 27, 2023 - by Bill Jelen

As you get started with DAX, you are going to find yourself using the CALCULATE function. This function will perform a calculation while applying any number of filters. =CALCULATE(Sum(Field),Filter1, Filter2, Filter3).

Replace Calculated Fields with DAX »

March 24, 2023 - by Bill Jelen

The DAX formula language really shines when you use it to create a new measure for your pivot table. DAX measures are in the same genre as calculated fields, but are infinitely more powerful.

Feature X Won’t Work in Power Pivot »

March 23, 2023 - by Bill Jelen

This is not a regular pivot cache pivot table that you’ve been using for the last 10 years. That data in your Excel worksheets is now in the Data Model. Even though the Data Model is stored inside of your Excel file, it gets treated like external data.

Microsoft Excel Inside Out (Office 2021 and Microsoft 365)

December 2021

Dive Into Microsoft Excel for Office 2021 and Microsoft 365 and really put your spreadsheet expertise to work. This supremely well-organized reference packs hundreds of timesaving solutions, tips, and workaroundsall you need to make the most of Excels most powerful tools for analyzing data and making better decisions.

MrExcel 2022 Boosting Excel

April 2022

The 6th Edition of MrExcel XL, updated with new functions released for Microsoft 365.

Master Your Data with Power Query in Excel and Power BI

August 2021

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Cool Excel Sh*t

March 2021

Cool Excel Sh*t is designed with the Excel guru in mind, introducing advanced, creative solutions and hacks for the software's most challenging problems.

see more products »

Unapply a Filter Using DAX

More Reading

Featured Products