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

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.

    • Gazzer's avatar
      Gazzer
      Icon for Resolver II rankResolver II
      8 years ago

      Couldn't you group the payroll data with Min (Start Date) and Max (End Date) before attempting to join the two tables? Assuming that start/end dates are the only columns which vary within each group of company IDs, that is.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        So in other words, using the 2008 start date and the 2016 end date as the overall start/end date? If I could do that, that'd be great! I'm just not sure how to go about doing that...