How Filter() function affecting All() Function

[kelvin-lkhca]

Hi All Experts,

Below is the PBI reportI have created for illustration purpose. There is a month slicer in the report. However, I need to have the full year revenue number which is not affected by the selection of the month in the slicer. 

Below are the two measures I created. The measure 1 still interact with the 'Month' slicer although I use All('Date'[Month]), however, the measure 2 works well and meet my need.

The difference between measure 1 and measure 2 is only the filter on the Data[Account] = "Revenue". I use filter() for measure 1 but skip the filter() function for measure 2.

I can't figure out  how filter() function affect the All() in the case above, can you please help?

Measure 1 - All() with Filter() function in the Measure

Measure 2 - All() without Filter() function in the Measure

Data Table

Date Table

Model View

[Anonymous · Accepted solution]

Hi @kelvin-lkhca ,

The core of the issue you're experiencing relates to how the 'Filter()' function interacts with the 'All()' function within the context of a measure and slicer selections. When you use the 'All()' function, you're essentially instructing Power BI to ignore filters that might be applied to the column(s) specified within the 'All()' function. This is typically used to calculate aggregate values over all the data, ignoring any slicer or filter context.

However, when you introduce the 'Filter()' function into the mix, you're applying a new filter context to the calculation. Even though you're using 'All()' to ignore certain filters, the 'Filter()' function applies a new filter which then becomes part of the calculation context.

In your Measure 1, the use of 'Filter()' alongside 'All()' means that while you're asking Power BI to ignore slicer selections on the 'Date'[Month] column, you're simultaneously applying a filter on the 'Data'[Account] column. This creates a specific context that still respects the filter applied by the 'Filter()' function.

For Measure 2, by omitting the 'Filter()' function, you're allowing the 'All()' function to work unimpeded, thus ignoring all slicer selections and calculating the value over the entire dataset as you intended.

To achieve your goal of having a full year revenue number unaffected by the month slicer, while still applying specific filters like 'Data[Account] = "Revenue"', consider restructuring your DAX formula to ensure that 'All()' is applied in a way that truly ignores all unwanted filters. One approach could be to use 'CALCULATE()' in combination with 'All()', explicitly defining the filter within 'CALCULATE()' to ensure it applies only where you intend.

For example:

Total Revenue = CALCULATE(SUM(Data[Revenue]), ALL('Date'[Month]), Data[Account] = "Revenue")

This formula ensures that the revenue is summed over all months, regardless of slicer selection, but still filters the data to include only "Revenue" accounts.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kelvin-lkhca]

@Anonymous  Great explanation! Thanks a lot for that!