cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rohitMe Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
Stachu Super Contributor
Super Contributor

Re: Lookup fucntion

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!

Proud to be a Datanaut!

6 REPLIES 6
Stachu Super Contributor
Super Contributor

Re: Lookup fucntion

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!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Lookup fucntion

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.
rohitMe Regular Visitor
Regular Visitor

Re: Lookup fucntion

Hi @v-piga-msft and @Stachu

 

This is a snapshot of my data 

Picture1.pngTable 1Picture2.pngTable 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 Super Contributor
Super Contributor

Re: Lookup fucntion

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!

Proud to be a Datanaut!

rohitMe Regular Visitor
Regular Visitor

Re: Lookup fucntion

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

Highlighted
Stachu Super Contributor
Super Contributor

Re: Lookup fucntion

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!

Proud to be a Datanaut!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 397 members 3,791 guests
Please welcome our newest community members: