Writing LOOKUPVALUE Using CALCULATE

[DevadathanK]

Hello Community!

<Message removed>

Thanks for any help!

[Greg_Deckler · Accepted solution]

Use MAXX(FILTER(...)...) instead of LOOKUPVALUE for your situation.

[amitchandak · Accepted solution]

@DevadathanK , you can try these kind of options

Across table data movement with filter

New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])
New Column = maxx(filter(table1,table1[Col1]= earlier(table1[col1]) && table1[Col1]= earlier(table1[col2]) ),table1[required_col])

Inside same table get the values

maxx(filter(table,table[Indicator no]=earlier(table[Indicator no]) && table[code]=earlier(table[code])
&& table[Qtr No] =(earlier(table[Qtr No])-1)),table[Actual])

With date

countx(filter(table,table[sub]=earlier(table[sub]) && table[BG]=earlier(table[BG]) && table[DIV]=earlier(table[DIV])
&& table[period]=earlier(table[period]) && table[updated]<=earlier(table[updated])),table[updated])

[DevadathanK · Accepted solution]

I used CALCULATE to modify the functionality of LOOKUPVALUE

https://community.powerbi.com/t5/Desktop/Unable-to-create-a-column-by-looking-up-values-in-a-another...

Please refer the above link!

[amitchandak · Accepted solution]

@DevadathanK , you can try these kind of options

Across table data movement with filter

New Column = maxx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])
New Column = maxx(filter(table1,table1[Col1]= earlier(table1[col1]) && table1[Col1]= earlier(table1[col2]) ),table1[required_col])

Inside same table get the values

maxx(filter(table,table[Indicator no]=earlier(table[Indicator no]) && table[code]=earlier(table[code])
&& table[Qtr No] =(earlier(table[Qtr No])-1)),table[Actual])

With date

countx(filter(table,table[sub]=earlier(table[sub]) && table[BG]=earlier(table[BG]) && table[DIV]=earlier(table[DIV])
&& table[period]=earlier(table[period]) && table[updated]<=earlier(table[updated])),table[updated])

[Greg_Deckler · Accepted solution]

Use MAXX(FILTER(...)...) instead of LOOKUPVALUE for your situation.