Forum Discussion

nagaraj007's avatar
nagaraj007
Icon for Post Patron rankPost Patron
5 years ago
Solved

Lookup value not showing correct data

Hello All,

 

I am trying to get the list name from other table called List, which contains word (ex Petrol ) and against that expense name (ex: Petrol = Vehicle Maintanance).

 

In my main table there is sentence which contains word called petrol, but its picking the wrong expense name.

 

Please advise.

Need Help
Show and Tell
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, nagaraj007 

     

    Since there is more than one matching value in your data, you need to modify the code.

    Like this:

    Column = 
MAXX (
TOPN (
1,
FILTER (
ADDCOLUMNS (
tblLookUp,
"pos", SEARCH ( tblLookUp[List], PettyCash[Description], 1, 0 )
),
[pos] > 0
),
[pos], ASC
),
tblLookUp[Expenses]
)

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.


    Best Regards,

    Community Support Team _ Janey

10 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    nagaraj007 , I doubt the description might have a word conveyance in it.

     

    You can also try

    maxx(filter(tbllookup,search(tbllookup[list], prettycash[description],,0) >0),tbllookup[expense])

    • nagaraj007's avatar
      nagaraj007
      Icon for Post Patron rankPost Patron
      5 years ago

      Hi Amit,

      Attached image has the description wherein amont word comes first before petrol word, but however the lookup value is picking based on petrol not based on amont.

       

      Please advise

       

       

      • nagaraj007's avatar
        nagaraj007
        Icon for Post Patron rankPost Patron
        5 years ago

        Formula used is

         

        ExpCat =
        LOOKUPVALUE(
        tblLookUp[Expenses],
        tblLookUp[List],
        FIRSTNONBLANK(
        FILTER(
        VALUES(tblLookUp[List]),
        SEARCH(tblLookUp[List],PettyCash[Description],1,0)
        ),
        1)
        )
         
         
         
        Please advise, as we have to present the data..