Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help linking tables together - ambiguity

Hi,

I am a long time Excel user coming over to Power BI. I definitely see the potential with Power BI and love what it can do. However, I am struggling with its strict table relationships and unsure how to get around it. 

 

I have three tables:

  • Employee:  Employee ID and the Org that this employee belongs to
  • Client:  Client ID and the Org that this client belongs to 
  • Interaction:  List of service tickets that lists the client who made the request and the employee who handled the ticket

My desired output is to get by Org, the count of clients, count of interactions its clients requested, and how many employees it has. I know the Org IDs are related between Client and Employees but trying to tie them together yields a relationship ambiguity error. The resulting output is that I don't get the Employee counts by Org but only see the total. 

 

In Excel, I would just vlookup based on Org ID to get both Client and Employee count but don't know how to handle this situation here. 

 

Thank you!

George

 

 

 

 

 

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    Anonymous based on your requirement, i think the relationship between employee and interaction is not required. and make relationship active between org and employee.

  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous -

    If I understand your requirements correctly, you could remove the relationships to org, and instead make a relationship between org and interactions. 

    So interactions would be the fact table, and org, emp, and client are dimension tables. 

    Then you can use DISTINCTCOUNT on empid and clientid from the Interactions Table, and use org name from the Org table.

    Cheers!

    Nathan

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous based on your requirement, i think the relationship between employee and interaction is not required. and make relationship active between org and employee.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous remove link between employee and interactions and make relationship active between org and employees.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous -

    If I understand your requirements correctly, you could remove the relationships to org, and instead make a relationship between org and interactions. 

    So interactions would be the fact table, and org, emp, and client are dimension tables. 

    Then you can use DISTINCTCOUNT on empid and clientid from the Interactions Table, and use org name from the Org table.

    Cheers!

    Nathan

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you all! Yes I didn't really need that relationship to exist in the example above.

      From browsing around the forum, another solution that works well is using the "userelationship" function to force an inactive relationship to be active.