cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Lookupvalue value between two values

Hi all, 

 

In one table I have the Age of IDcontacts, and in another table I have AgeRanges such as : 

 

IdAgeFromAgeToDescription
10100-10
2112011-20
3213021-30
4314031-40
5415041-50
6516051-60
7617061-70
8718071-80
9819081-90
1091200> 90

 

I would like to connect both tables. I would like to do the equivalent of a VLOOKUP TRUE in excel, such as 'if the value Age is between 0 and 10, then display'0-10'

 

I tried a column such as : 

AgeID = IF([Age]<11,1,IF([Age]<21,2,IF([Age]<31,3,IF([Age]<41,4,IF([Age]<51,5,IF([Age]<61,6,IF([Age]<71,7,IF([Age]<81,8,IF([Age]<91,9,10))))))))) 

so I could link it to the Age range table, but the rationale is wrong and I believe it's possible to do it with a Lookupvalue. 

 

Any ideas? 

Thanks in advance! 

 

2 ACCEPTED SOLUTIONS
Community Champion
Community Champion

@Anonymous Please try this as a "New Column"

 

Assuming you age descriptions in a separate table and you want to retrieve the age description into another table based on the age.

 

AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo]))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@Anonymous But if you see the screenshot that I've posted, it has age values like 25, 35 which are in between the age range but not as starting values. If you want to handle null values in age, then add another condition to exclude blank values.

 

AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo] && Test123Data[Age]<>BLANK()))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
New Member

Hi how to write that in Power Query M Language to create a new column please ?

Community Champion
Community Champion

@Anonymous Please try this as a "New Column"

 

Assuming you age descriptions in a separate table and you want to retrieve the age description into another table based on the age.

 

AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo]))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

@PattemManohar, actually it works only partially. With this formula it only notes as 11-20 those who's age is 11, 21-30 those who's age is 21, etc. Also, it notes 0-10 those who's age is Blank. 

 

Any ideas? 

@Anonymous But if you see the screenshot that I've posted, it has age values like 25, 35 which are in between the age range but not as starting values. If you want to handle null values in age, then add another condition to exclude blank values.

 

AgeDesc = CALCULATE(VALUES(Test123Lkp[Description]),FILTER(Test123Lkp,Test123Data[Age]>=Test123Lkp[AgeFrom] && Test123Data[Age] <= Test123Lkp[AgeTo] && Test123Data[Age]<>BLANK()))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

It works nicely, thanks a lot! 

Super User II
Super User II

@Anonymous

 

For ranges you need to use the AND statement

e.g IF ( AND([Age]>=21, [Age]<30), 1, ....

 

I dont think your logic is correct

________________________


If this post helps, then please Accept it as the solution to help other community members find it more quickly


Click on the Thumbs-Up icon if you like this reply.


Check out my COVID-19 Report in Data Stories Gallery:    COVID-19 Report

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors