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], "," )

     

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], "," )

     

    • Aravindbala's avatar
      Aravindbala
      Frequent Visitor
      3 years ago

      thank you