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
DylanLeong
New Member

Lookup tier with multiple results expected

I need a dynamic lookup on a table consisting of tiers

 

Table A: 

Type 

Holding Company 

Company 

Spend 

Date 

 

Table B

Type 

Holding Company 

Company 

Rebate

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
3 REPLIES 3
v-yuaj-msft
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,

Yuna

v-yuaj-msft
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)

v-yuaj-msft_0-1613541852109.png

 

Best Regards,

Yuna

 

amitchandak
Super User
Super User

@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

 

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

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.