cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bw70316 Member
Member

Two Tables with ID Relationship Won't Work

I have a Contracts and an Actuals Items Table for my Vendors. They both have vendor numbers. The Contracts table provides me with the initial amount while the Actuals Table supplies the debits from the contract. I have dates for both connected through a dates calendar, but when I try to bring in the Actual Items there is no relationship. When I try to create the relationship it says I need unique items. I don't have the credentials to access the dable on that side, so how do I create the relationship between the two in PBI?

8 REPLIES 8
Super User
Super User

Re: Two Tables with ID Relationship Won't Work

Hi @bw70316,

Create a table of unique values with the vendors number then relate this table with the other two and this will make the bridge between the other 2 tables.

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bw70316 Member
Member

Re: Two Tables with ID Relationship Won't Work

I have tried to create a separate table with the Dax equation below and got the table pictured below. I still am unable to create the relationship and get the same error. How do I Relate the tables?

Dax.pngVendor List.png

Super User
Super User

Re: Two Tables with ID Relationship Won't Work

Hi @bw70316 ,

 

You should link the Actuals Table to this new table and the Contracts also to this new table based on Vendor.

 

Can you share a sample of the data?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bw70316 Member
Member

Re: Two Tables with ID Relationship Won't Work

Contracts Table:

Vendor NumberVendor NameStarting AmountDates
1000010706Bob200001/1/2019
1000010707Barry300001/2/2019
1000010708Brian150001/3/2019

 

Actuals Table

 

Vendor NumberVendor NameDebitsDates
1000010706Bob-10,0003/1/2019
1000010707Barry-12,0003/2/2019
1000010708Brian-8,0003/3/2019

 

I would like to demo how much has been spent out of what was initially set aside for the contract. My real data is much more complex with a calendar table tying everything together.

Super User
Super User

Re: Two Tables with ID Relationship Won't Work

Hi @bw70316 ,

 

Check the PBIX file attach with a model setup based on your data sample.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
bw70316 Member
Member

Re: Two Tables with ID Relationship Won't Work

I have a table for contracts and actual items. My Contracts table houses the initial amount of the contract. The Actuals Table include the Debits from the contracts. I would like to make a connection via my vendor numbers (vendors are who the contracts are assigned to). I have tried to different ways to make a connection. One with the Summarize function:

VendorTypes = SUMMARIZE(
        UNION(
            SUMMARIZE('Contracts','Contracts'[Vendor Name],'Contracts'[VENDOR]),
            SUMMARIZE('Actual Items','Actual Items'[Vendor Name],'Actual Items'[VENDOR])
        ),
        [Vendor Name],[VENDOR])

And another with Append, a video I found here: https://www.youtube.com/watch?v=vAvQ8pCnWDk 

 

In which I create two new tables from the contracts and actuals vendor numbers, remove the duplicates, and append them. After removing the null values, I am able to create a relationship, but I cannot get the values I would like. Below is a screenshot of my relationships and a screen shot of the not working chart.Relatinoships.png

 

As you can see I have a Many to 1 between my Append 1 table and Contracts with "Both" as Cross filter tied together with the vendor number that has null values removed. There is also a connection to my Vendor Number in my Actuals Table, but it is a Many to 1 with "Single" Corss Filter Direction but the relationship is inactive? Not sure why?

 

charts.png

 

On the left is the contract amounts consisting of the Contracts' Vendor Name and Transaction Amount. The right the actuals Vendor name and amount spent. The last number is my attempt at putting the Contracts Transactions into the Actuals Chart that is comprised of The Actuals Table's Spent and Vendor Name. Ideally, I would like to able to something like this:

VendorInitial AmountSpentRemaining
Benniegirl Healthy Lifestyle1689095007390
Super User
Super User

Re: Two Tables with ID Relationship Won't Work

Hi @bw70316 ,

 

Can you share your data file by private message?

 

So I can check the model, believe that your issues is related with the other relationship you have on your model, but those are not visible on the print screen.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




bw70316 Member
Member

Re: Two Tables with ID Relationship Won't Work

I made my append table and Actuals Relationship active by deleting the relationship with the Calendar that was causing an issue. Additionally, I created a relationship cross filter relationship between contracts and append1 to go with the actual items and append1 cross filter of both.