Getting values from a table with original ID and renewal ID in same table

[awff]

Hi all,

I've spent way too long trying to work out how to do this...

Basically, I have a query using Salesforce Objects where in the opportunity we link the renewal opportunity (renewalOpportunity__c) to the original opportunity (Id). To get the product items,there is a relationship from the opportunity to the quote, then to the quote line items.

 I can get all the products, units and price of the the original opp, but how can I get the ones for the renewal opp for comparison to see the unit and price change between the original and renewal?

Ideally, the result would be as below:

Original Opp	Renewal Opp	Product	Original Units	Original Units	Renewal Units	Renewal Units
Original 1	Renewal 1	A	2	1000	3	1100
Original 1	Renewal 1	B	3	1200	3	1300
Original 1	Renewal 1	C	3	1500	3	1550
Original 2	Renewal 2	A	1	1050	1	1100
Original 2	Renewal 2	B	1	900	2	900
Original 3		C	1	1500

Hopefully this makes sense as I am going crazy as I feel it can 't be as hard as I am making it !

Thank!

[RossEdwards]

Your modelling is correct.  What happens with you put the columns into a Table visual and mark the fields as "Do Not sumamrize"?

[awff]

Hi Ross, Thanks for your response! The result remain unchanged.

I'm not sure if there is a measure that can be used to get the specifically use the Opportunity[renewalOpportunity__c] id to match with the Quote[OpportunityId], then get the values such as unit price and quantity from the Quote line item table.

I was able to get the results with some complex conditons in Python, but seems like i'm going in circles in Power Query and DAX

[RossEdwards]

I've taken your basic design and simplified the columns for an example.  Don't worry if my exact columns aren't right, what matters is which tables i'm pulling stuff from so you can see that this works.  Hopefully we can work backwards and figure out where you have gone wrong.

Here is the basic modelling in my example:

Here is a very basic layout in a table without using any measures:

Without using any measures, can you get a table that shows anything like this using just columns?  One test can be to start with all of your columns and remove all the measures. See if you get a table that is right.

[v-tianyich-msft]

Hi @awff ,

Is it possible to share the sample file, or share the data in a table?

Best regards,
Community Support Team_ Scott Chang