Lookup value based on multiple criteria.

[Anonymous]

hi guys, 

I have this formulated column to gather some values from a table into another depending on a single criteria:

Last AIF VA MCLS = calculate(FIRSTNONBLANK('Last Observation VA Mcls'[AIF],1),FILTER(all('Last Observation VA Mcls'),'Last Observation VA Mcls'[Market]='Last Observation QF BKG CLS'[Market]))

I still have to input VA AIF into 'Last Observation QF BKG CLS" however the criteria to match values has to be Market and another one which is AP.

So AP data is (as you can imagine) in both tables Last AIF VA MCLS and QF BKG CLS (and also AIF VA SCLS). So the nmodel should match the value into QF BKG CLS only if Market and AP are correct. 

Thank you 

[hmt1]

For anyone else that ended up here, LOOKUPVALUE lets you keep adding search columns and search values.

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value> [, <search2_columnName>, <search2_value>]… [, <alternateResult>] )

[v-piga-msft]

Hi @Anonymous,

You could have a good look at this video LOOKUPVALUE with multiple columns first.

If you still need help, could you share some data sample which your reproduce your scenario and your desired output, so that we can help further investigate on it?

Best  Regards,

Cherry

[Anonymous]

Hi Cherry, 

yes I did and those solution didn't worked at all. That's why I used the Filterall alternative. I looked at several "LOOKUPVALUE" tutorials but they led me nowhere.