Add Filter Without Compromising Performance
You’ve built a clean star schema in Power BI. Dimension and fact tables are linked with one-to-many, single-direction relationships. This setup makes DAX calculations efficient and easy to follow.
Then the client asks for a new feature: a slicer that hides categories based on another slicer. For example, they want to see which colors were sold when filtered by size. They also want to avoid empty results when users pick invalid size–color combinations.
The first thing that comes to mind
You might try switching single-direction relationships into bidirectional ones. But this breaks star schema principles for a small feature. Bidirectional filters also demand more processing and may slow down reports. Even worse, they can return unexpected results. Often, you only notice the issue once the numbers are wrong.
Visual-level filtering
A safer option is visual-level filters. They let you control slicer behavior without touching the fact table.
Select the visual, open the filter pane, and apply your measure in “Filters on this visual.”
Create a measure that checks whether the size–color combination exists in the fact table. For example:
Total Price = SUM ( 'FACT Sales'[Price] )
If the result is blank, the combination doesn’t exist. A simpler check is to count rows:
Records = COUNTROWS ( 'FACT Sales' )Apply the filter with “is not blank.” Now the “Size” slicer reacts to the “Colour” slicer. Sizes not sold in the chosen color disappear from the list.
Modifying relationships at run time
Modifying relationships at run time
Sometimes the client wants dimension-to-dimension analysis. For example: how many colors were sold for each size? Normally this requires a bidirectional relationship, which can slow down large models.
Instead, you can use DAX to change filter direction only during query time. The CROSSFILTER function helps. It alters relationships only while evaluating the measure:
Different Colours Sold =
CALCULATE (
DISTINCTCOUNT ( 'DIM Colour'[Colour] ),
CROSSFILTER (
'DIM Colour'[Colour],
'FACT Sales'[Product Colour],
BOTH
)
)
This counts colors in the dimension table while making the relationship bidirectional only for the measure. Place it in a table with “Size” and you’ll see how many colors were sold for each one.
Wrapping up
SStar schema modeling is best in almost every scenario. Don’t compromise your model. Instead, use virtual relationships with functions like TREATAS, USERELATIONSHIP, and INTERSECT. These allow targeted filtering only where needed, keeping performance high.
👉 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
