Forum Discussion

nikhil_chauhan's avatar
nikhil_chauhan
Regular Visitor
8 years ago
Solved

using range lookup in power bi

Hi i wanted to know ,, how could i use lookup in power bi   eg if sheet1 in excel contained column A                 COLUMN B          COLUMN C roll number from     roll number to       CLASS   ...
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi nikhil_chauhan,

     

    In Power BI, we could use LOOKUPVALUE Function (DAX) to match the value.

     

    Assuming that you have two tables like below.

    By my test, you could create a calculated column with the formula below.

     

    Column =
LOOKUPVALUE ( T1[CLASS ], T1[roll number from], 'T2'[Roll NO] )

    Then you will match the CLASS value in T1.

     

     

    Please note that you should change the Roll No data type from whole number to text, because lookupvalue does not support comparing values of type text with values of type Integer. 

     

    In addition, you could have a reference of this article.

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi nikhil_chauhan,

 

In Power BI, we could use LOOKUPVALUE Function (DAX) to match the value.

 

Assuming that you have two tables like below.

By my test, you could create a calculated column with the formula below.

 

Column =
LOOKUPVALUE ( T1[CLASS ], T1[roll number from], 'T2'[Roll NO] )

Then you will match the CLASS value in T1.

 

 

Please note that you should change the Roll No data type from whole number to text, because lookupvalue does not support comparing values of type text with values of type Integer. 

 

In addition, you could have a reference of this article.

 

Best Regards,

Cherry

souldiv's avatar
souldiv
Frequent Visitor
6 years ago

Hi

 

It does not work as lookupvalue is based on equality. In this case we need to match a value and a range

For example: 30 should return B