Forum Discussion

Sabsy's avatar
Sabsy
Frequent Visitor
7 years ago
Solved

LOOKUPVALUE with multiple results

Hi all,

 

I am new to Power BI so appologies if this is a silly question but I've been stuck on finding a solution and could really use some guidance. 

 

I have two tables that do not contain a unique key, One table contains value Customer Plan (new) and I would like to use it to look up against Customer Plan (old) in a different table to return a list of contracts. 

 

For example,

 

Table 1:

Customer_Plan (old)            Contract ID

123_A                                     A1

123_B                                     A2

123_B                                     A3

 

Table 2:

Customer Plan (new):

123_B

 

The idea is that I would like to generate an output that tells me that customer plan (new) "123_B" in table 2 has two contract IDs "A2" and "A3" when referencing customer plan (old) in table 1. The problem when I try and use function LOOKUPVALUE it returns multiple results and cannot display this in one column. My question is, how can I get this to either generate multiple columns (depending on the total number of results) or generate a new table of results so that I can get the output I am looking for. 

 

24 Replies

    • Sabsy's avatar
      Sabsy
      Frequent Visitor
      7 years ago
      It doesn’t at the moment but I could create a unique ID.
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

         

        Will it be OK, if i can write a measure to get all reults for a particular ID in one cell (seperated by commas)?

  • Sabsy's avatar
    Sabsy
    Frequent Visitor
    7 years ago

    Thank you PattemManohar for your suggestion, I have tried this and it has worked and provided all results into one cell.

     

    Thank you Ashish_Mathur for your suggestion to merge the queries. As I am quite new to Power BI, I wasnt familiar with how this worked but have managed to also replicate a solution using this function. My question now is that now I have structred the data in a way where i have a table of results within Table 1. Is there a way to print the contract ID list of results in table 1 with each contract ID having its own column. The customer/plan table may have a varied different number of results (max would be 10).

     

    Table 1

     

    Customer/Plan (As-Is) table (row 1) within Table 1

     

    Example output...

     

    Customer/Plan (As-Is)          Contract ID1       Contract ID2       Contract ID3     Contract ID4

    8300002592_AF                   T-009141            T-001871             T-0018172      T-18191

     

    Thanks.     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

       

      Yes, that should be possible.  Paste the base data here so that i can copy that in Excel.

      • Sabsy's avatar
        Sabsy
        Frequent Visitor
        7 years ago

        Hi,

         

        Example data is below, you will need to merge tables 1 and 2 first.

         

        Table 1
        Customer/Plan (To-Be)
        8300002592_AF
        8300002602_AF
        8300002222_AF

        Table 2
        Customer/Plan (As-Is)    Contract ID
        8300002592_AF            T-0019141
        8300002592_AF            T-0018171
        8300002592_AF            T-0018172
        8300002592_AF            T-0018122
        8300002602_AF            T-0019149
        8300002602_AF            T-0018177
        8300002602_AF            T-0018194

         

        Thanks,

        David.