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

Summarize/Append failing to get a Working Relationship without Unique Values

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
2 REPLIES 2
Highlighted
bw70316 Member
Member

Model and Relationship Help - Need Unique Values

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. 

 

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
Community Support Team
Community Support Team

Re: Summarize/Append failing to get a Working Relationship without Unique Values

Hi @bw70316 ,

It seemed you want to create a many-to-many relationship, could you have checked your relationship with loops like below:

1.PNG

You could refer below link about the relationships:

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships#adjusting-cross-fi...

And if you still could not solve the problem, could you please share your file to have a test if possible?

 

Regards,

Daniel He

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