using range lookup in power bi

[nikhil_chauhan]

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   

1                                20                           A

20                               40                        B

40                                60                       C

and if wanted to know which roll no is in which class i would simple use lookup(3,a1:a30,c1:c30)

and i would get the desired answer

how do i replicate the same in power bi

thanks

pn, i have the sheet with rolls nos and class

and another sheet containg various different roll no ,, where i should i get the class 

thank you

[v-piga-msft · Accepted solution]

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]

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

[nikhil_chauhan]

hey @v-piga-msft

thanks a lot,,, that solved my problem

[v-piga-msft]

Hi @nikhil_chauhan,

It is glad that we can help. Only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[kv]

The DAX lookup function does not seem to work for a lower and higher range as specified in the solution.