FILTER vs KEEPFILTERS

Filter context is contextual data that can be filtered by. This can be a column of values in a data model (filter context) or a category in a visual (visual filter context)
Row context creation (iteration) means that Power BI will no longer calculate the total or max of a single column but will now calculate its expression per row. It then overlays an overarching base expression, such as SUM, on top of this outcome. By calculating per row (iteration) instead of applying a calculation to one column, filter context is included. Even where no filter context was present in the first place.
Filter context creation means that Power BI opens the possibility to modify and/or filter already existing filter context (such as a category in a visual).

Example: Only customer A needs to be visible.

To be able to see only customer A, you can use the expression FILTER or KEEPFILTERS. Both expressions have the purpose of filtering data, but they work in a different way.

The FILTER expression is a table filter expression. It returns a table object in the background that contains only the rows that meet the criteria. This means that when using the FILTER expression, Power BI creates a virtual table in the background. This opens the possibilities for more complex modifications, such as references to multiple measures or columns. Please note that the virtual table can also cause a deterioration in performance, just like with the iteration expression SUMX.

The KEEPFILTERS expression is a filter customization expression. His goal is to preserve existing filters in a calculation. It ensures that any filters applied to the table or columns are not overwritten by subsequent calculations. It does not return a virtual table in the background. This means that KEEPFILTERS’ performance will work better technically but is less suitable for complex modifications.

FILTERS or KEEPFILTERS?

For single-column filters, using KEEPFILTERS is often a better choice than writing an explicit filter function. KEEPFILTERS also ensures improved performance. For complex modifications, FILTER is a better solution.

Sharing is caring!

Have a look at my other blogs

ISFILTERED

You probably know that the ISFILTERED function returns TRUE when the specified table or column is being filtered directly. But did you know that if you add multiple ISFILTEREDs together, ISFILTERED will not return true or false, but will return a number. You can combine this returned number with a switch function to influence your total levels.

Un-Bold Row Headers

Have you ever created a matrix visual, with hierarchy, where your row headers are formatted 𝐛𝐨𝐥𝐝? You can not seem to turn if off, because

Alignment of visuals

Have you ever heard of the “Align” button? It helps you to align visuals, no more precision drag-and-drop…

It also helps to distribute visuals horizontally of vertically over a canvas. This will give an overall alignment of visuals which would make your report so much cleaner.