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?
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?
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?
|Vendor Number||Vendor Name||Starting Amount||Dates|
|Vendor Number||Vendor Name||Debits||Dates|
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.
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.
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?
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:
|Benniegirl Healthy Lifestyle||16890||9500||7390|
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.
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.