FILTER vs KEEPFILTERS vs VALUES – short comparison – Trainings, consultancy, tutorials

FILTER vs KEEPFILTERS vs VALUES – short comparison

In this article, you should learn which filter function to choose in which situation. Take this as a quick overview, certainly not complete.

What happens if we don’t use any filter at all?

No filter = SUM(my_table[Price])

If we don’t use any filter, the entry will look like this:

Filter without a filter function

The shortest possible notation of the filter in measure looks like this. No special function is used, only the itself.

Filter without filter = CALCULATE ( SUM ( my_table[Price] ), my_table[Color] = “Red” )

The result seems to be confusing, because:

values respect the restrictions given by DAX condition,
values respect the visual filters, with the exception of the column on which we wrote the filter criteria in the measure.

that is, there are only Alfa Romeos in the Alfa Romeo group, but there are always reds in the individual colors – even if it is a row with a blue car.

You would have the same result with this (it would just be unnecessarily long, don´t do it):

Filter with filter and ALL =
CALCULATE (
SUM ( my_table[Price] ),
FILTER ( ALL ( my_table[Color] ), my_table[Color] = “Red” )
)

Filtrering with FILTER or KEEPFILTER functions

Syntax is like this:

Filter with filter =

CALCULATE (
SUM ( my_table[Price] ),
FILTER ( my_table, my_table[Color] = “Red” ))

Even better, you can use KEEPFILTERS. It will work the same as FILTER, but with a shorter syntax and better performance for larger tables: The word “KEEP” is derived from the fact that the filter remains above this column – it is not removed.

Keepfilters =
CALCULATE ( SUM ( my_table[Price] ), KEEPFILTERS ( my_table[Color] = “Red” ) )

In both cases, the result will look like what you usually need. So the number:

will respect all visual filters
and at the same time it will respect the filter defined by the measure.

Filtering using the VALUES function or similar

The VALUES function can be used like this. The point is that it will return the context normal behaviour, i.e. the filters of visual will be taken into account. Usually this can (and should) be replaced with KEEPFILTER or FILTER.

Values =
CALCULATE (
SUM ( my_table[Price] ),
my_table[Color] = “Red”,
VALUES ( my_table[Color] ))

Resume – which one to choose?

If you don´t want to remove the visual context (and, usually, u don´t), use KEEPFILTERS.

Otherwise (probably only in exceptional cases), use the way without a special filtering function.

In this article, you should learn which filter function to choose in which situation. Take this as a quick overview, certainly not complete.

What happens if we don't use any filter at all?

No filter = SUM(my_table[Price])

If we don't use any filter, the entry will look like this:

Filter without a filter function

The shortest possible notation of the filter in measure looks like this. No special function is used, only the itself.

Filter without filter = CALCULATE ( SUM ( my_table[Price] ), my_table[Color] = "Red" )

The result seems to be confusing, because:

values respect the restrictions given by DAX condition,
values respect the visual filters, with the exception of the column on which we wrote the filter criteria in the measure.

that is, there are only Alfa Romeos in the Alfa Romeo group, but there are always reds in the individual colors - even if it is a row with a blue car.