Showing results for 
Search instead for 
Did you mean: 
New Member

Lookup tier with multiple results expected

I need a dynamic lookup on a table consisting of tiers


Table A: 


Holding Company 





Table B


Holding Company 



Amount from 

Amount to

Date from 

Date to 


The user should be able to filter by Type, Holding Company ,Company and see the total spend for a particular date range selected from Table A in their report


The total amount (for the date range selected) should look up table B and find the correct rebate amount in the tier for selection.  
The user should be able to select a Company within the Holding company and that spend total should correspond to the tier for that Company in Table B to obtain the rebate - The total spend will differ on a Holding Company versus a Company. Mutiple values is expected as the user should be able to select multiple Holding Companies in their selection


TypeHolding CompanyCompany Spend Date
GasHLD - CompanyACompanyS 6,6752020-01-29
GasHLD - CompanyBCompanyT 9,2622020-01-29
GasHLD - CompanyACompanyV 2,4302020-01-29
GasHLD - CompanyBCompanyW 5,7852020-01-29
GasHLD - CompanyACompanyX 5,2392020-01-29
GasHLD - CompanyBCompanyZ 12,8352020-01-29
GasHLD - CompanyACompanyS 11,0152020-02-06
GasHLD - CompanyCCompanyT 7,2272020-02-06
GasHLD - CompanyDCompanyV 14,1042020-02-06


Holding CompanyRebate% Amount from  Amount To Date From Date To
HLD - CompanyA1.00% 20,000 40,0002020-01-012020-12-31
HLD - CompanyA3.00% 40,001 60,0002020-01-012020-12-31
HLD - CompanyA5.00% 60,001 9,999,9992020-01-012020-12-31
HLD - CompanyB2.00% 50,000 75,0002020-01-012020-12-31
HLD - CompanyB4.00% 75,001 100,0002020-01-012020-12-31
HLD - CompanyB6.00% 100,001 250,0002020-01-012020-12-31
HLD - CompanyC2.50% 150,000 175,0002020-01-012020-12-31


Expected output:

  Rebate applRebate bandCalc Rebate
 HLD - CompanyA  89,8785% 60,001 4,493.90
 HLD - CompanyB  99,3424% 75,001 3,973.68
 HLD - CompanyC  129,2960% 150,000 -00
 HLD - CompanyD  99,1092% 80,000 1,982.18
Community Support
Community Support

Hi @DylanLeong ,


Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


Best Regards,


Community Support
Community Support

Hi @DylanLeong ,


Sorry for that I'm still confused to get your desired output. Could you please explain with an example? (eg. the first row of your desired table)



Best Regards,



Super User IV
Super User IV

@DylanLeong , You need ti few common dimensions like date, Type, Holding Company , Company (in one ore moew dimensions ) and join both tables with them and analyze them together

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors