Re-organizing Info Based on Criteria

[ehwood]

Hello PBI Community,

So I'm having trouble finding a solution to this issue. My data comes in the form of Table 1. Orders with multiple components are split into seperate lines. In this example, only product A is a regular product and everything else you can only purchase along side product A (or you can purchase product A alone).

Table 1:

.

I want to use this data to fill a newly created Table 2. The goal is to identify the main product and subsequent "add-ons".

I have Order_Id and Product Name already completed in Table 2, but cannot list the add-ons. Here's an example of what I'm trying to get to.

Table 2: (GOAL)

I've messed with a formula like....

Add-On 1 = CALCULATE(MAXX(RELATEDTABLE('Orders'),'CD: Orders'[product_name]),

'CD: Orders'[product_id] <> 0,
)

... but I don't think it's the right way to think about the issue and I don't know how to pull the subsequent add-ons. Maybe it has something to do with the RANK function?

Anyways, if anyone could advise , I would appreciate it greatly.

Thanks,

Ehwood

[Ashish_Mathur · Accepted solution]

Hi,

Here is the result i got.  You may download the PBI file from here.