Lookupvalue equivalent to search for text values

[gvg]

Hi DAXers,

I am wondering is there a way to search for a text string in DAX and return a value like with LOOKUPVALUE? I believe I can not use LOOKUPVALUE because ir requires a scalar search value. Looking for something like LOOKUPTEXT.

Thanks.

[MFelix · Accepted solution]

Hi @gvg,

Assuming you want to return the full data LOOKUPVALUE works fine with text if you only want part of tge value you need to use SEARCH.

Regards,
MFelix

[MFelix · Accepted solution]

Hi @gvg,

Assuming you want to return the full data LOOKUPVALUE works fine with text if you only want part of tge value you need to use SEARCH.

Regards,
MFelix

[gvg]

How does that work? Assume I have two tables:

Table1                                                       Table2

Product name   Amount                           Product name  Discount

A                       100                                  A                       10%

B                        200                                 B                        12%

LOOKUPVALUE(Table1[Amount],Table1[Product name],Table2[Product name]) doesn't work for me complaining that the third argument needs to be scalar value. I need to write something like LOOKUPVALUE(Table1[Amount],Table1[Product name],MAX(Table2[Product name]) ), but this doesn't work either because Product name is not numeric field.  Am I loosing something here?

[MFelix · Accepted solution]

Hi @gvg,

You are making a measure right? In dax you can use max and.min also with text fields, so if.ypu use the.last sintax should work.

[gvg]

OK, it works for a standalone measure. My problem was that I've been using it as part of another formula.

Thanks!

[MFelix]

Hi @gvg,

This sould work even as part of another measure depending on the way you set up your measure.

Can you please elaborate how you are doing that?

Regards,

MFelix

[gvg]

Won't be able to reproduce it. I was experimenting with lots of solutions.