Forum Discussion

rohitMe's avatar
rohitMe
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Lookup fucntion

Hi all,   I want to know whether I can perform a lookup in a table in the following way:   -All the values I want to search are listed in one column(Note: I want to search multiple values using o...
Need Help
Tips and Tricks
Tutorial Requests
  • Stachu's avatar
    Stachu
    8 years ago

    this will give all the customer Ids

    Customer Id 2 =
VAR CurrentMobileNr = Table1[Customer Mobile]
RETURN
    CALCULATE (
        CONCATENATEX ( Table2, Table2[Customer Id], "," ),
        Table2[Customer Mobile] = CurrentMobileNr
    )

    EDIT
    no duplicates version

    Customer Id 2 =
VAR CurrentMobileNr = Table1[Customer Mobile]
VAR NoDuplicates =
    FILTER (
        SUMMARIZE ( Table2, Table2[Customer Mobile], Table2[Customer Id] ),
        Table2[Customer Mobile] = CurrentMobileNr
    )
RETURN
    CONCATENATEX ( NoDuplicates, [Customer Id], "," )

     

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi rohitMe,

 

Have you solved your problems?

 

If you have solved, could you share your solution or always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

In addition, you could have a good look at this article which including the example of Lookup value function.

 

If you still need help, could you share your data sample and you desired output, so that we can help further investigate on it?

 

Best Regards,

Cherry

rohitMe's avatar
rohitMe
Icon for Advocate I rankAdvocate I
8 years ago

Hi v-piga-msft and Stachu

 

This is a snapshot of my data 

Table 1Table 2

 

As you can see there are duplicate values in my customer mobile problem. Table 1 has only those entries with status Cancelled while Table 2 has entries with Status Closed as well as Cancelled. Now my search criteria is Customer Mobile column of Table 1 only values to be searched in table 1. The return value should be the Customer Id from Table 2 to be returned in Table 1.

I was able to achieve this using the vlookup function in excel but it also returned the value of the Customer Id corresponding to the one it was searching. Is there any way to avoid it. Contact me for any further clarification.

 

Thanks and Regards

Rohit

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I would suggest creating calculated column in Table1, with following syntax

    Customer Id 2 = 
VAR CurrentMobileNr = Table1[Customer Mobile]
RETURN
CALCULATE(FIRSTNONBLANK(Table2[Customer Id],TRUE()),Table2[Customer Mobile]=CurrentMobileNr)
    • rohitMe's avatar
      rohitMe
      Icon for Advocate I rankAdvocate I
      8 years ago

      HI Stachu

       

      The solution that you gave returns only one Customer Id but it should return list of all the Customer Id of the Customer Mobile which is repeated in table 2.

       

      Your Solution does help me find out the Customer Mobile which are duplicate.

       

      Thanks and Regards

      Rohit

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        this will give all the customer Ids

        Customer Id 2 =
VAR CurrentMobileNr = Table1[Customer Mobile]
RETURN
    CALCULATE (
        CONCATENATEX ( Table2, Table2[Customer Id], "," ),
        Table2[Customer Mobile] = CurrentMobileNr
    )

        EDIT
        no duplicates version

        Customer Id 2 =
VAR CurrentMobileNr = Table1[Customer Mobile]
VAR NoDuplicates =
    FILTER (
        SUMMARIZE ( Table2, Table2[Customer Mobile], Table2[Customer Id] ),
        Table2[Customer Mobile] = CurrentMobileNr
    )
RETURN
    CONCATENATEX ( NoDuplicates, [Customer Id], "," )