cancel
Showing results for
Did you mean:
Highlighted
Helper II

## "LOOKUP" as on Excel ???

Hello everyone,
i wish to use a formula, as on Excel, that does "LOOKUP" (not lookupvalue).
my need is to determine whether x is between 2 specific numbers and return the value in the RESULT column that refers to it.

for example: if x=5 return 10
if x=130 return 150 etc.

my lookup table:

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: "LOOKUP" as on Excel ???

Following is the sample lookup table I have created:

and this is what you can get using the DAX calculated column:

There is no relationship between these two tables and the values are not unique

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

10 REPLIES 10
Highlighted
Super User II

## Re: "LOOKUP" as on Excel ???

Hello @akfir ,

Try the solution offered in a community post earlier:

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Highlighted
Helper II

## Re: "LOOKUP" as on Excel ???

In your given case - the values were unique dates, and probabely it has a relationship in the model.
In my case, the values may repeat themselves and no relationship can be made.

thanks again,
Amit

Highlighted
Super User II

## Re: "LOOKUP" as on Excel ???

Following is the sample lookup table I have created:

and this is what you can get using the DAX calculated column:

There is no relationship between these two tables and the values are not unique

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Highlighted
Helper II

## Re: "LOOKUP" as on Excel ???

perfect! thanks!
i was just miswriting your command.

thanks again!

Highlighted
Helper II

## Re: "LOOKUP" as on Excel ???

Thanks to your solution i managed to get the desired result, but i still have on issue regarding the SUM of these values in a matrix.
it does not show the correct sum of the per row measures.

i think it is something to do with "IF(HASONEFILTER(.....))" but i can not generate the SUM condition that will show me correct result.

Amit

Highlighted
Super User II

## Re: "LOOKUP" as on Excel ???

Can you share the sample pbix file?

Cheers!
Vivek

https://www.vivran.in/

Highlighted
Helper II

## Re: "LOOKUP" as on Excel ???

unfortunately no. anything else may help? maybe a snapshot:

Highlighted
Super User II

## Re: "LOOKUP" as on Excel ???

This is not helpful in understanding the issue.

Can you create a excel file with sample values of table Loading and Rounding Rules, along with the expected output?

Cheers!
Vivek

https://www.vivran.in/

Highlighted
Helper II

## Re: "LOOKUP" as on Excel ???

can i maybe send you my pbix privately?

thanks

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors