How to Use Dynamic Filtering – the Right Way – in Power BI 

Mandes Stoner   •   08.02.2019

Filtering using a slicer in Power BI can be a useful tool for business users to display their data based on specific measures, dimensions, etc. However, an issue can arise with filtering by common attributes from multiple tables. These tables may have builtin relationships already, but there are other factors such as cardinality that prevent Power BI from being able to filter all related tables dynamically on the same attributes if not set up correctly. 

Dynamic Filtering Using Slicer’s and Data from Multiple Tables 

One easy way to get around this issue is by using “bridge” or “lookup” tables. Bridge tables work just how they are named, as bridges to define relationships between tables with common attributes. Below is a simple example of two bridge tables created to join the tables, ProjectedResources and Resource Allocation on the common attributes of application and month. 

Power BI

As you can see, the bridge tables connect with the main tables using a 1:* cardinality. It is also important to note that relationships between tables and bridge tables should cross filter one way from one to many. This can be achieved by selecting each relationship and making sure that “Single” is selected under the “Cross filter direction” dropdown selection. Cross filtering in both directions with not allow the slicer tool to filter dynamically on the table associated with that relationship. 

From here, business users should now be able to filter by both ProjectedResources and Resource Allocation tables on these attributes with both data sets adjusting accordingly. To try and paint this picture better, imagine a line graph visual displaying ProjectedResources and Resource Allocation over months. Using a slicer tool, a business user can now filter the graph based on specific applications. For our example, we will say a business user would like to filter on the Ambulatory application. By selecting the Ambulatory application using the slicer tool, the line graph visual will now dynamically filter both lines for ProjectedResources and Resource Allocation by only this single application. 

All in all, bridge tables can be a beneficial strategy when dealing with data sets of multiple tables that need to be filtered dynamically on common attributes. 


Mandes Stoner

Consultant

Share This Article


At Talavant, our goal is to help you discover, ask and answer the right questions – so you get more value from your data to drive your business forward. You’ll gain a competitive edge with faster, more relevant analytics. It’s all part of our focused, holistic approach that goes beyond tools.

Ready to get started?

Contact Us