Lookup Formula

[Chandrashekar]

Hello Team,

Can you pls help me to get excel formula in Power BI.

Attaching Excel(with formula) and PBIX file:

Excel: Excel File 

PBIX: PBIX File 

[amitchandak]

@Chandrashekar , Can you explain what you need.

Power bi has lookupvalue and search, find, containssring

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

https://docs.microsoft.com/en-us/dax/search-function-dax

Also, we can search value on column in another column like

new column =

var _col = [columnA] //Current row value

return

maxx(filter(Table, [columnB] =_col ), [ColumnC]) // Search  full column B and return Column C

[Chandrashekar]

Hello Amit,

 In Excel Sheet1 contains Group, Country code and Country name.

In sheet2 I need formula where formula should search [Group] from Sheet1 -> [Group] Sheet2 and get value from Sheet1(Col B).

Regards,

Chandrashekar B

[amitchandak]

@Chandrashekar , These will become two tables in power bi

a new column in table 1 using DAX

sumx(filter(Table2, Table2[Group] = Table1[Group]), table2[value])

or

MAXX(filter(Table2, Table2[Group] = Table1[Group]), table2[value])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Chandrashekar]

Hello Amit,

I can able to get result for one set of data but same formula not working for another set where I have short description.

Formula1 =

var searchdesc=Sheet1[short desc]

Return

maxx(filter(Sheet2,containsstring(Sheet2[Keywords],searchdesc)),Sheet2[IType])

Regards,

Chandrashekar B

[Chandrashekar · Accepted solution]

Hello Amit,

Thanks. It got resolved.

Regards,

Chandrashekar B