MrExcel.com - Your One Stop for Excel Tips & Solutions

Unapply a Filter Using DAX

March 28, 2023 - by

Unapply a Filter Using DAX

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.
  • Ready for something amazing? The filters in CALCULATE have the power to tell the external outside force to not apply a certain filter. If that formula up in F4 used a filter of Month=”Feb”, the filter in the Calculate formula would override the filter from the row label in D4. Let me show you an example.

Consider this figure.

The pivot table starts in D with a list of sectors. The Sum of Revenue is in E and you can see various amounts, like 3.2 Million for Apparel, 2.2 Million for Appliance, down to a total of 37.2 Million. In Column F, the measure is called Apparen Sector. The answer for every row in column F is 3.2 Million. Column G is called All Sectors and the answer for every row is 37.2 Million. In column H, a calculation for Percent of Apparel shows this sectors sales as a percentage of the Apparel Sector. Formulas are shown at the bottom. For F use =CALCULATE(Sum(Sales[Revenue]),Sector[Sector]="Apparel"). For G, use =CALCULATE(Sum(Sales[Revenue]),ALL (Sector)
Figure 1066. These DAX Measures unapply a filter.
  • Column E, Sum of Revenue is a regular old field where I took the Revenue field from the field list and put it in the values drop zone. Column E respects the filters in the slicer and the filters of the row labels in column D.
  • Column F is a DAX Measure where I used Calculate to override the filter on sector. No matter what label is over in column D, the DAX measure in column F will filter sector to Apparel. Column F still continues to respect the month slicer, though. The formula for the measure in F is =CALCULATE(SUM(Sales[Revenue]),Sector[Sector]=”Apparel”)
  • Column G is a DAX Measure where I wiped out the Sector filter by using ALL. Every row in column G is going to show the total for all sectors, even though the row label in D5 says that this row is for Appliance. The formula for the measure in G is =CALCULATE(SUM(Sales[Revenue]),All(Sector)). Note that this formula still respects the filter applied in the month slicer.
  • Column H is the actual useful field. It takes the revenue for this sector and divides it by the revenue for the Apparel sector. The formula here re-uses the existing DAX measure from column F: =sum(Sales[Revenue])/Sales[ApparelSector]. Of course, this formula still respects the month filter applied from the slicer.

As you change the filters other than sector, all of the formulas update. Here is the same pivot table filtered to June, July, and August.

If you change the month slicers, all of the calculations from the previous figure update.
Figure 1067. Change any filters other than Sector to recalculate.

The previous topic showed how you could unapply the Sector filter to get all sales for another sector. What if you need to compare sales for this date to all dates in the month? Or a running MTD number? Or sales from a prior year? DAX introduces many new time intelligence functions that can be used to unapply a filter.

If you wanted to compare calculate Month to Date sales, you should be able to refilter the date using calculate: =CALCULATE(Sum(Sales[Revenue]),DATESMTD(Sales[Date])). The DATESMTD() function returns a list of dates to consider.

This article is an excerpt from Power Excel With MrExcel

Title photo by Stephen Kraakmo on Unsplash