Advanced Data Filtering in Power BI: How to Use Dynamic Parameters for Creating Universal Reports?
Not so long ago I had to create a universal report in Power BI that would be used by several different clients. All of them used Microsoft Planner as a data source, and the data view was also the same. However, later some of our new customers 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 and yet universal that would work for everybody.
The following data filtering criteria have been defined:
- It is not mandatory.
- It must not return an error if there are no enities of entities don’t have required fields.
- It must work with several entities, several 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 parameters of the filter.
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 which 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 an 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.