Forum Discussion
Power BI data modeling question
To avoid bidirectional filtering, performance issues, and ambiguity, the recommended best practice is to structure your model into a proper star schema by creating a dedicated Dimension Table for shared keys (e.g., a DimOrder table) or ensuring a central bridge table connects to both fact tables. Do not create slicers directly from fact tables (Returns).
Recommended Solutions:
Create a DimOrder Table (Best Practice): Create a new table DimOrder consisting of distinct Order IDs from both the Orders and Returns tables.
Create a 1-to-many single-direction relationship from DimOrder to Orders (on OrderID).
Create a 1-to-many single-direction relationship from DimOrder to Returns (on OrderID).
Use the Region from People (connected to Orders) and Order ID from DimOrder in your slicers.
DAX TREATAS (Alternative): If rearranging the model isn't possible, create a DimOrder table and use DAX to map the relationship.
dax
FilteredOrders =
CALCULATE(
[Total Orders],
TREATAS(VALUES('DimOrder'[OrderID]), 'Orders'[OrderID])
)
CROSSFILTER in Measures: To make a slicer on Returns affect Orders, use CROSSFILTER in a specific measure to change the filter direction only for that calculation, maintaining overall performance.
Hii Mahadevbisht879
The recommended approach is proper star-schema modeling, not TREATAS. Keep People >> Orders >> Returns as single direction (dimension >> fact) relationships and do not use slicers directly from fact tables. Instead, create a shared dimension (e.g. Order or Customer/Order bridge) that both Orders and Returns relate to, and use slicers only from dimension tables. This allows People and Returns-related filtering to work together naturally without bidirectional relationships, avoids ambiguity, and gives the best performance. Use TREATAS only as a last-resort workaround, not as a primary modeling pattern.
If I use Region from the People table and Order ID from the Returns table as slicers, they do not filter each other.