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

     

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