Forum Discussion

Mahadevbisht879's avatar
Mahadevbisht879
New Member
7 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.

sevenhills's avatar
sevenhills
Super User
7 months ago

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


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