Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

You can't create a relationship between these two columns...

So I get why this is happening even though they are the exact same numbers in both columns from both tables:

There are duplicates in my CService table because since a company can have multiple services other than payroll, they will be listed more than once of course. My question is, are they any workarounds to this? I need to be able to make a connection between these two tables based on the Company ID.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous,

    My mistake, there is a syntax error in my original DAX. Please use DAX below instead, replace CBIZ with your own table.

    NewTable = 
FILTER(
DISTINCT(
UNION(
DISTINCT(CBIZ[CompanyID]),
DISTINCT(CService[co])
)
),
NOT(ISBLANK(CBIZ[CompanyID]))
)



    Regards,
    Lydia

13 Replies

  • jday's avatar
    jday
    Icon for Helper I rankHelper I
    8 years ago

    Hey, 

     

    All you need to do is create a seperate table with distinct company id's and join both of your current tables to that table.   Power bi only supports 1 to many relationships.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I thought about that but I still run into an issue because a company can also have two payroll services with two different sets of start and end dates. I need to be able to consider those as well with the end result being needing those dates for retention rates.

      • jday's avatar
        jday
        Icon for Helper I rankHelper I
        8 years ago

        I don think I am following correctly then. 

         

        If you have your two tables that can't join and you join with the distinct list of company id's you can indirectly build a relationship with your original tables.  

         

        If you have the distinct table you can enable cross filtering on your join to go both ways and reference anything from either table to do your calculations or summaries.  

         

        Can you provide an example of your data and what you want it to do exactly and I see if I can create a mock up for you. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,


    Create a new table using DAX below. Then create relationship between the new table and the other two tables.

    NewTable = 
FILTER(
DISTINCT(
UNION(
DISTINCT(CBIZ[CompanyID]),
DISTINCT(CService[co])
)
),
NOT(CBIZ[CompanyID])
)



    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous attempted to do this but it doesn't let me finish typing the formula out:

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Anonymous I'm sorry, it actually did work. Now I'm just getting this error message: