Power BI data modeling question

[Mahadevbisht879]

Hello,

I am working on a Power BI data model and need some advice.

I have three tables named People, Orders, and Returns.

People is related to Orders with a single direction relationship.
Orders is related to Returns with a single direction relationship.

My requirement is to use slicers from both People and Returns.
For example, Region from People and Order ID from Returns.
Both slicers should work together and filter results correctly.

I want to do this without using bi directional filtering.
I want to avoid performance and ambiguity issues.

What is the recommended best practice for this scenario.
Should I use a DAX approach such as TREATAS or is there a better modeling pattern.

Thank you for your help.

[V-yubandi-msft]

Hi @Mahadevbisht879 ,

May I know if the issue has been resolved? If you need any additional information or clarification, please let us know.

Thank you.

[V-yubandi-msft]

Hi @Mahadevbisht879 ,

@srlabhe  , has correctly pointed out the issue and shared a few steps to follow. Have you tried those steps, and did they help? If you are still facing any issues or need additional information, please let us know.

Thanks for your valuable response@srlabhe .

Regards,

Yugandhar.

[srlabhe · Accepted solution]

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.

[sevenhills]

I will approach like this to begin with:

Dims

• Dim Person = Dim Customer = concept to understand fast
  • Natural Key = Customer Number
  • SK = Person ID
• Dim Product
• Dim Date = Date dimension :: role playing dimension
• Dim Order Status
• Dim Return Reason

Facts

• Fact Orders ... Order Key :: OrderDate_SK, Person_SK, Product_SK, Order_Number (Degenerate), Quantity, Sales_Amount
• Fact Returns ... Order Key (referenced) :: ReturnDate_SK, Customer_SK, Product_SK, ReturnReason_SK, Original_Order_Number (Degenerate), Returned_Quantity, Refund_Amount

Degenerate Dimensions

• Transaction Profile Dim :: Order Key :: Original Order Number, a union from Fact Orders, Fact Returns. 
  • Rare cases, we can see data in returns but not in orders. Dont ask me why, I have seen in practical.
  • Other reason is later-arriving facts i.e., orders in the dimensions

Note:

• A product i.e, part of the order may be ordered on Date1. Delivered on Date2. Returned on Date3. you need to consider for your measures.
• assumption here: Fact Orders and Fact Returns are at line item level and not different levels i.e., summary level. 

Data Model: Relationships:

• Dim Person, Dim Date, Dim Product, Dim Trans Profile, Dim Order Status --> Fact Orders
• Dim Person, Dim Date, Dim Product, Dim Trans Profile, Dim Return Reason --> Fact Returns Degenerate dim: Dim Trans Profile

Thanks


--------------------

Assuming you are not doing accumulating shapshot fact, read for more details: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimen...

[cengizhanarslan]

Use a star-style design with shared dimensions

• Keep People as a dimension

• Create an Order dimension (unique OrderID)

• Relate both fact tables to that Order dimension

Example:

• DimPeople[PersonID] 1→* FactOrders[PersonID]

• DimOrder[OrderID] 1→* FactOrders[OrderID]

• DimOrder[OrderID] 1→* FactReturns[OrderID]

[Mahadevbisht879]

If I use Region from the People table and Order ID from the Returns table as slicers, they do not filter each other.

[burakkaragoz]

Hi @Mahadevbisht879 ,

@rohit1991  is absolutely correct about the Star Schema approach. To answer your specific question about TREATAS versus modeling: Do not use TREATAS for this. TREATAS is complex, harder to maintain, and slower than a physical relationship.

Here is the complete breakdown of the Best Practice (Star Schema) and the Immediate Fix for your current setup.

1. The Best Practice: Shared Dimension (Star Schema)

You currently have a "Chained" model (People -> Orders -> Returns). The professional way to fix this is to restructure it so People and Returns don't rely on Orders as a bridge.

• Create a Date Table: Connect both Orders[Date] and Returns[Date] to it. Use the Date slicer to filter both.

• Create a Master Orders Table: If you need to slice by Order ID, create a distinct list of Order IDs (a Dimension table) and connect it to both Orders (Fact) and Returns (Fact).

  • Result: Slicing this new Dimension table filters both tables instantly without bidirectional ambiguity.

2. The Immediate Fix: Filter Flow (Waterfall Logic)

If you cannot change the model right now, you must understand that filters only flow Downhill.

• People filters Orders

• Orders filters Returns

The Problem: You are likely slicing using columns from the Returns table. Returns is at the bottom, so it cannot filter "up" to Orders. The Solution: Change your slicers to use columns from the Orders table.

• When you pick a Region (People), it filters Orders.

• The Orders table then filters Returns.

3. How to "Filter Back Up" (Without Bi-Di)

If you need the People slicer to only show people who actually have Returns (filtering uphill), do not enable bidirectional relationships.

Instead, use a Visual Level Filter:

1. Create a simple measure: Return Count = COUNTROWS('Returns')

2. Select your People Slicer.

3. Drag [Return Count] into the "Filters on this visual" pane.

4. Set it to "is not blank" or "> 0".

This forces the slicer to check the data availability dynamically without hurting performance like Bi-Directional filtering would.

---

If this helps clarify the modeling best practices, please consider giving a Kudo! 
This response was assisted by AI for translation and formatting purposes.

[rohit1991]

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.

[Mahadevbisht879]

If I use Region from the People table and Order ID from the Returns table as slicers, they do not filter each other.