Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akfir
Helper V
Helper V

"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
vivran22
Community Champion
Community Champion

@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
vivran22
Community Champion
Community Champion

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

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

vivran22
Community Champion
Community Champion

@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

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

vivran22
Community Champion
Community Champion

@akfir 

Can you share the sample pbix file?

 

Cheers!
Vivek

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

@vivran22 

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

1.PNG

vivran22
Community Champion
Community Champion

@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

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

 

thanks

@vivran22 
i think the issue is that it is taking the sum (which i expect) and goes to lookup table and tries to find a result within the range.

i want the sum to just sum regularly the values underneath. 

perfect! thanks!
i was just miswriting your command.

thanks again! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors