Lookup value not showing correct data

[nagaraj007]

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.

[v-janeyg-msft · Accepted solution]

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

[amitchandak]

@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]

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]

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..

[v-janeyg-msft]

Hi, @nagaraj007 

You need to modify your measure, but I need to know what your data is in powerbi.  Please upload some insensitive data samples and expected output. So we can help you soon.

Best Regards,

Community Support Team _ Janey

[nagaraj007]

Hi @v-janeyg-msft ,

Attached is the Download data .

Report has 2 tabs (One sheet is raw data) and sheet is List data.

Based on raw data description , it should pick the 1st word available from sheet 2 and show .

1st line item has 2 words (petrol and misc) but the output should be based on first word petrol ..

[v-janeyg-msft]

@nagaraj007   I need related tables(and not lists) data in powerbi desktop fields, 'tb1lookup' and 'pettycash'. 

Best Regards,

Community Support Team _ Janey

[nagaraj007]

Hi @v-janeyg-msft ,

I have attached the PBIX file

[v-janeyg-msft · Accepted solution]

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

[nagaraj007 · Accepted solution]

thank you very much @v-janeyg-msft  got the required output...

[nagaraj007]

Hi @amitchandak ,

Could you please help me