Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:
1.PNG
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.

1 ACCEPTED SOLUTION

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yuezhe-msft attempted to do this but it doesn't let me finish typing the formula out:

1.PNG

Anonymous
Not applicable

@v-yuezhe-msft I'm sorry, it actually did work. Now I'm just getting this error message:
1.PNG

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jday
Helper I
Helper I

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
Not applicable

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.

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
Not applicable

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:

1.PNG

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.

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
Not applicable

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

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
Not applicable

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors