Add Filter Without Compromising Performance
Does the following scenario sound familiar? You have a neat and clean star schema semantic model in Power BI, with dimension and fact tables prepared with one-to-many, single-direction relationships. This setup ensures your DAX calculations run smoothly and efficiently, making the data intuitive and easy to understand.
But then, the client comes up with a new feature request: they want one of the slicers to hide certain categories based on another slicer. Specifically, they want to know which colors were sold when filtered by Size. Additionally, they want to prevent the situation where someone filters by a size and colour combination, resulting in an empty set of records.
The first thing that comes to mind
Your first instinct might be to go into the model viewer and modify those neat single-direction relationships to bidirectional ones. However, doing this throws all the star schema model theory out the window just for a small requested feature.
The main issue is that bidirectional relationships often require more processing, which can impact the performance of your report, especially if you need to use complex DAX formulas. There’s also the risk of unexpected results. Bidirectional relationships can filter related tables in ways that might produce unanticipated filtering and retrieve the wrong number of records. Depending on your model, you might not notice this behaviour until it’s too late.
Visual level filtering
The simplest approach is to use visual-level filters to achieve the desired behaviour in your slicers without affecting the filtering on the fact table.
As you probably know, you can apply filters to a single visual element in your report. Simply select the visual you want to filter, then click on the filter pane to expand the bar and apply the filter.
We want to create a measure to filter this visual and drop it into the ‘Filters on this visual’ well. In our scenario, we’ll use the fact table as a bridge between the two dimensions, returning only those rows from the dimension tables where the combination exists in the fact table.
While this might sound complex, it essentially means identifying which combinations of records in the fact table do not return a blank. This can be easily achieved with the following formula:
Total Price = SUM ( ‘FACT Sales'[Price] )
When the sum of the price returns a blank, we’ll know that the combination of size and color does not exist in the sales table.
However, depending on the data you’re working with, this kind of formula could return a blank even if the combination does exist. In those cases, a good alternative is the following:
Records = COUNTROWS ( ‘FACT Sales’ )
Again, simply counting the rows in the fact table will give us a clear indication of whether the size and color combination exists or not.
Once you’ve created this DAX measure, place it in the visual-level filters well and set the condition to ‘is not blank’.
Now the “Size” slicer will react to the selection of the “Colour” slicer, and those sizes that were not sold in the selected colour will disappear from the list.
Another approach: Modifying the relationship at run time
Now, let’s say the customer wants to perform a dimension-to-dimension analysis. For example, they want to know how many different colors were sold for each selected size.
In this scenario, to have a formula that counts the colors for each size, we typically need to use a bidirectional relationship. While it’s possible to use the fact table and summarize its data to find the solution, doing so with a heavy or complex table might require significant processing power, negatively impacting performance.
To achieve the best performance while getting the desired results from your model, you can leverage the query engine with proper filter propagation.
One option in DAX is to change the filter direction using the CROSSFILTER function. This function takes two column references and a direction definition, altering the relationship between the columns just for the evaluation of the measure. This allows you to activate or deactivate relationships or bidirectionality as needed.
Different Colours Sold =
CALCULATE (
DISTINCTCOUNT ( 'DIM Colour'[Colour] ),
CROSSFILTER (
'DIM Colour'[Colour],
'FACT Sales'[Product Colour],
BOTH
)
)
We are performing a very simple DISTINCTCOUNT of colors (note that this is done in the dimension table itself, not in the fact table) while modifying the relationship between the DIM Color and FACT Sales tables to be bidirectional.
When this measure is placed inside a table with the sizes field, it will produce the following analysis:
Wrapping up
Star schema modeling is king in almost all situations and scenarios. Instead of compromising your model, consider using virtual relationships between fields to achieve the results you want.
Other useful DAX functions you might want to explore include TREATAS, USERELATIONSHIP, and INTERSECT. These functions allow you to apply specific filtering between tables during measure evaluation without altering your model. This approach allows you to use a particular filtering strategy only for the measure where it’s needed.
👉 If you’re interested in discovering how Microsoft Power BI can benefit your business, schedule a free workshop with our experts now: www.mdw-group.com/contact
