Power BI data modeling question

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 responsesrlabhe .

Regards,

Yugandhar.

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]

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.

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.

I will approach like this to begin with:

• 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

• 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

• 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

• 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

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.