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

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

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

For the CService query, use the Group By transform for the 'co', 'service', 'serviceLevel' columns and aggregate Min for 'startDate' and Max for 'endDate'. I expect you might also need to group by 'companySet' and 'Active Check' but it will depend where/how you use those columns.

 

Then when you come to the merge stage as shown in the image in your original question, you should only have one version of each company. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I see. I did this and I still run into the same error. The issue arises because I assume if a company has a 'payroll' service and another service type, by default it would have to create two separate records for it right? It would group one record by 'payroll' service and another by 'xxxxx' service. Makes it tricky :/.

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

    Well, one way or another you need to make a unique set of records for this data.

    Is the payroll service value required in this report? If not, then leave it out of the Group By.

    If it is needed, then you may need to do some work to set which of the values takes priority.