Advanced Data Filtering in Power BI to Create Universal Reports

Anna Shalomova

PPM Consultant

Not so long ago I had to create a universal report in Power BI that would be used by several clients. All of them used Microsoft Planner as a data source, and the data view was the same. However, some of our new customers later started requesting additional data filtering options. Since we found it very time-consuming to create and support personal report templates for all customers, we decided to create something simple yet universal that would work for everybody.

Looking for Power BI consultant?

Contact us

The following data filtering criteria have been defined

• It is not mandatory.
• It must not return an error if there are no entities of entities that don’t have the required fields.
• It must work with several entities and fields, and it should be possible to define several values for each field.
• It must be simple: the only purpose is to check if the given value equals one of the filter’s parameters.

Solution

Let’s create an optional text parameter, CustomFilters:

This parameter will contain a string that defines the description of our filter: entity/field/value. For this structure, the following format has been chosen: Entity->Column=Value1||Entity->Column=Value2…

Since our structure will be defining many filters, it’s optimal to use a table view (let’s name it CustomFiltersTable):

And here is a code fragment that allows to do the above:

Now we need only two functions: one for applying the filters to the entities and another – to consistently apply filters to every column of the entity recursively. We do that because Power Query M has no loops at all. Let’s name them accordingly:

• ApplyCustomFilters(EntityName, EntityTable) – this function will check if there are filters for the given entity, and if yes – run the next function. If the filters for the entity are not defined – this function will return EntityTable without change.
• ApplyCustomFilter(EntityName, Columns, EntityTable) – this function will read all the filter values for every column name in the Columns list parameter and apply them to EntityTable. After that, the name of the column to which the filter has been applied will be removed from the Columns list and the function will run again with a shortened list of column names until the list is empty.

Use the following code to create these two functions:

Now it’s time to do some data filtering. Just run the function and enjoy!

Pro’s and Con’s

The report we created is extremely easy to support, it gives the user the ability to customize the needed data filtering criteria without modifications inside the report. The filtering mechanism itself is quite simple and elegant, but yet it will be a solution for many cases.

At the same time, this approach needs additional information about entities and their fields to be provided with the report, so that users could customize the filter string without the assistance of developers.