cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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:
lookup.PNG

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: "LOOKUP" as on Excel ???

@akfir 

 

Following is the sample lookup table I have created:

Lookup Table.PNG

 

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

 

Sol.PNG

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

Relationship.PNG

 

Cheers!
Vivek

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

https://www.vivran.in/

Connect on LinkedIn

View solution in original post

10 REPLIES 10
Highlighted
Super User II
Super User II

Re: "LOOKUP" as on Excel ???

Hello @akfir ,

 

Try the solution offered in a community post earlier:

 

https://community.powerbi.com/t5/Desktop/Add-a-column-to-a-Data-table-from-another-lookup-table/m-p/...

 

Cheers!
Vivek

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

https://www.vivran.in/

Connect on LinkedIn

Highlighted
Helper II
Helper II

Re: "LOOKUP" as on Excel ???

Thanks for your quick reply.
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
Super User II

Re: "LOOKUP" as on Excel ???

@akfir 

 

Following is the sample lookup table I have created:

Lookup Table.PNG

 

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

 

Sol.PNG

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

Relationship.PNG

 

Cheers!
Vivek

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

https://www.vivran.in/

Connect on LinkedIn

View solution in original post

Highlighted
Helper II
Helper II

Re: "LOOKUP" as on Excel ???

perfect! thanks!
i was just miswriting your command.

thanks again! 

Highlighted
Helper II
Helper II

Re: "LOOKUP" as on Excel ???

Hi @vivran22 

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.

Thanks again in advance,
Amit

Highlighted
Super User II
Super User II

Re: "LOOKUP" as on Excel ???

@akfir 

Can you share the sample pbix file?

 

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

Highlighted
Helper II
Helper II

Re: "LOOKUP" as on Excel ???

@vivran22 

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

1.PNG

Highlighted
Super User II
Super User II

Re: "LOOKUP" as on Excel ???

@akfir 

 

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/
Connect on LinkedIn

Highlighted
Helper II
Helper II

Re: "LOOKUP" as on Excel ???

can i maybe send you my pbix privately? 
afterwards please delete it from your pc.

 

thanks

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

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?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

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

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.