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...
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

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

        I need a table with more than just distinct ID's. I need their start and end dates as well. So when I say a company can have more than one payroll service with multiple start/end dates instead of just one, this is what I mean:

        So this company would be in the list twice since it had two payroll services at two different points in time. I need to keep my start and end dates because I am trying to calculate customer retention based off start and end dates for all companies.

        The table I'm trying to join it to is from another data source but with the same company ID's. The ID's are only listed once in this dataset because they are from survey responses rather than our SQL database. What I'm ultimately trying to achieve is figuring out customer retention by our Managers which I would be able to figure out if I could just find a way to connect these two tables.