LOOKUPVALUE with multiple results

Hi Ashish,

I am not sure that will work and I created the second table to try and simplify the issue. 

I have tried to simplify this problem to explain but let me give you some further details. The problem is that customer plan old and new could have mutliple values and are not unique, this is because there are more attributes in table 1 due to the granularity of data. Even if they are in the same table you have the same problem because a lookup will return multiple values that i cannot structure as an output. 

To explain this further, Customer Plan (new) originates from table 1 as a calculated column in table 1. In table 1 there is a list of impacted rows where the customer plan old and new do not match.  I want to look up all old customer plans using the new customer plan and return a list of contract IDs. This is a many to many relationship and what creates the complication. It is a combination of contract ID and product that make the row unique but customer plan old and new can exist in many rows. 

For example:

Table 1:

Contract ID             Product                 Customer_Plan (old)             Customer Plan (New)       Impacted

A1                             10                                   123_A                                       123_A                     No

A2                             10                                   123_A                                       123_A                     No

A3                             20                                   123_B                                       123_A                     Yes

A4                             30                                   123_B                                       123_A                     Yes                           

Desired output is to use the impacted column (Impacted = Yes) to find a list of contracts. To do this you will need to look up the customer plan new value against the old value and return the list of contracts and IDs. Example of desired output is below.  

Table 2:

Contract ID             Product           Contract ID (new)       Conrtact ID (new 2)        

A3                             20                              A1                           A2

A4                             30                              A1                           A2

As I am new to Power BI, I am not sure if I am going about this completely the wrong way. Alternatively, if we could combine the list of results for the new contracts id 1,2,3... etc into a single column that would also meet the requirement. 

Thanks,

David.