Forum Discussion

b-kopik's avatar
b-kopik
Helper III
2 months ago
Solved

PowerBI Relationships

I’m working with two datasets in Power BI that both contain multiple rows per employee, but represent different types of information. Dataset 1: Combined Headcount Data Every month, I l...
Need Help
  • Rupa01's avatar
    Rupa01
    2 months ago

    Hi b-kopik,

     

    In this scenario, I would avoid directly relating the two fact tables (Headcount & Campus) using a many-to-many relationship. Since both tables contain multiple rows per Employee ID, a direct relationship can create ambiguous filtering and incorrect results.

    A better approach is to model this as a star schema, using shared dimension tables. Microsoft also recommends organizing Power BI models with fact and dimension tables for better filtering, usability, and performance.

    Refer - Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

     

    ✔️Correct Approach for creating relationships - 

    DimEmployee[Employee ID] 1-* FactHeadcount[Employee ID]
    DimEmployee[Employee ID] 1-* FactCampus[Employee ID]
    DimDate[Date] 1-* FactHeadcount[Month/Date]
    DimDate[Date] 1-* FactCampus[Course Date]
     
    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer
b-kopik's avatar
b-kopik
Helper III
2 months ago

Thanks all for the suggestions! Here are some screen captures - all the relationships are active and working.

 

 

Except for the one highlighted:

 

Basically - The Business Unit is a table with only one column with all business units. There are no duplications. That is one of my slicer. So I don't understand why I can't connect it to the terminations table (to the business unit column in that table)?

 

Every time I try to make this relationship active, I get this msg:

 

 

 

  • Rupa01's avatar
    Rupa01
    Solution Sage
    2 months ago

    Hi b-kopik,

     

    The error is expected because Power BI detects two active filter paths between Business Area and Business Units. This creates an ambiguous relationship path, which is why Power BI forces one relationship to remain inactive.

     

    A star schema should not have circular or ambiguous filter paths. Follow single-direction 1-to-many relationships from dimensions to facts to maintain a clear and predictable filter flow. If multiple filter paths are needed, use a bridge table instead of bi-directional relationships to avoid ambiguity and keep the model clean and scalable. Microsoft's modelling guidance recommends this approach for both performance and usability.

     

    Recommendation -

    • Model Business Area, Business Units, Agencies, and Networks as dimension tables.
    • Keep fact tables such as Combined Headcount and Havas - Termination Information separate.
    • Avoid dimension-to-dimension relationships through fact tables.
    • If Business Area and Business Units genuinely require a many-to-many connection, introduce a bridge table instead of creating multiple filter paths.

     

    References -

     

    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer