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 built–in 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.
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.