Lookup with if condition to match two columns from different table.

[Anonymous]

Hello Communities,

Please see below requirements,

I have a two tabsheet in excel and I want to create LOOKUP WITH IF and I am not able to figure it out.How to create a formula?

Main Table:01-Accessories

Lookup Table:Accessories Historical Data.

Requirements:What I am trying do in formula, if CURRENT UI A(MAIN TABLE) column match with ACCESSORIES HISTORICAL UI A(LOOKK UP TABLE) then I want to AVERAGE HISTORICAL 2017 DUPLiCATE WITH CALULATION.

I have used below formula and it is generating an error,

Current= IF(LOOKUPVALUE('Accessories Historical Data'[ACCESSORIES HISTORICAL UI A],'Accessories Historical Data'[ACCESSORIES HISTORICAL UI A],

'01-Accessories'[Current UI A]=('Accessories Historical Data'[ACCESSORIES HISTORICAL UI A]),0))

Erro Message “The number of arguments is invalid. Function LOOKUPVALUE must have a value for each specified column reference.”

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the DAX below.

Current =
AVERAGEX (
    FILTER (
        'Accessories Historical Data',
        'Accessories Historical Data'[ACCESSORIES HISTORICAL UI A]
            = '01-Accessories'[Current UI A]
    ),
    'Accessories Historical Data'[HISTORICAL 2017 DUPLiCATE WITH CALULATION]
)

[Anonymous]

Thank you so much for your suggestions sam,it works really good for me.from the above issue i learnt that without lookup you can do lookup.Thanks Again

[Stachu]

so you want to show the average of 2017 for all the values that have a match, is that correct? no other 'else' criteria? 
I would do IF with COUNTA on RELATED/RELATEDTABLE - if count of rows in the related table is <> 0 then show average, else blank

[Anonymous]

Hi Stachu,

you are right, i want to match two columns from diffrent table and column match then i want to average 2017.

I am very new to dax.Can you please show me how can i write formula?

if(counnta(columnname>>>Do i need to put column from table 1 or column from 2) and where do i need to put RELATED.

Thanks