Forum Discussion

Mahadevbisht879's avatar
Mahadevbisht879
New Member
6 months ago
Solved

Power BI data modeling question

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 ...
Data Protection
  • srlabhe's avatar
    srlabhe
    6 months ago

    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.

burakkaragoz's avatar
burakkaragoz
Super User
6 months ago

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.