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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bw70316
Helper V
Helper V

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
MFelix
Super User
Super User

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

Hi @bw70316 ,

 

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

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.