cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Search for categories between two tables

I have two tables. Table A contains the rent of houses. Table 2 shows the limit values for the rental classes. 

Now I want to add the rental classes to table 1.

So in my example, house 1 falls in Group 4 and house 2 falls in Group2

In Excel this is easily done with the match-function. 

How can I do this in Power BI?

 

AHouseRent
 1602
 2500
 3975
 4650
 5524

 

 

BGroupRentMax
 Group1400
 Group2500
 Group3600
 Group4700

 

Hope you can help me on this!

1 ACCEPTED SOLUTION

Thanks Greg!

 

With one little adjudgment it works perfectly:

Rent Group = VAR MaxRent = MINX(FILTER(RentGroups,RentGroups[RentMax]>=(Houses[Rent])),RentGroups[RentMax])
RETURN LOOKUPVALUE(RentGroups[Group],RentGroups[RentMax],MaxRent)

 

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Rent Group = VAR MaxRent = MINX(FILTER(RentGroups,RentGroups[RentMax]>=MAX(Houses[Rent])),RentGroups[RentMax])
RETURN LOOKUPVALUE(RentGroups[Group],RentGroups[RentMax],MaxRent)

Nice job with posting the data and what you expect as a result.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Thanks Greg!

 

With one little adjudgment it works perfectly:

Rent Group = VAR MaxRent = MINX(FILTER(RentGroups,RentGroups[RentMax]>=(Houses[Rent])),RentGroups[RentMax])
RETURN LOOKUPVALUE(RentGroups[Group],RentGroups[RentMax],MaxRent)

 

View solution in original post

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