Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rohitMe
Advocate I
Advocate I

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 only one lookup function).

-The column in which I am looking the value are in a second table.

-The value I want to return are in the second table.

 

If there is a way to do this please share it with me. Please share an example with the solution. Do ask if you don't understand my Question. Also if there is an alternate way of doing this without using the lookup function please share

 

Thanks

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

7 REPLIES 7
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-piga-msft and @Stachu

 

This is a snapshot of my data 

Table 1Table 1Table 2Table 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
Community Champion
Community Champion

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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

thank you

 

Stachu
Community Champion
Community Champion

can you share example of the tables? otherwise it's not specific enough
in general I'd say that FILTER should do the job, but it depends on exact structures, joins, etc.

BTW for future reference - if you post sample tables, info on joins etc. it's much easier to anwser the question



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.