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
Bill_NYC
Regular Visitor

My two tables are many-many. How to make one-many with a slicer?

I have two tables with a common column 'category':

A: category, product, price

B: category, discount_level, discount_pct

 

Table A has multiple products per category and table B has multiple discount levels per category, so the relationship on category is many-many. 

 

Question: How can I calculate price*discount_pct based on a discount level selected by the user in a slicer?

 

Note: I have a solution but it is too slow.  The solution is to transpose the discount levels into columns, which makese the category in table B unique, then use SWITCH to choose the correct column.  I would like to avoid transpose to see if I can improve the performance.  (I believe the performance is slow using transpose because it must calculate discounted prices at every discount level first, then select the correct version according to the user selection using SWITCH.)

6 REPLIES 6
Bill_NYC
Regular Visitor

Thanks but the discount percentages are different for each category so a parameter won't work (unless I'm missing something).

Hi, @Bill_NYC 

Not sure what you want.

Does the following formula not work in your many-many table model?

Measure = MAX('Table A'[price])*MAX('Table B'[discount_pct])

 

Best Regards,
Community Support Team _ Eason

Whitewater100
Solution Sage
Solution Sage

Hi:

You could do something like creating a new table to be the brige table allowing for relatinoship measures.

 =DISTINCT(UNION(ALL(TableA[Category]), ALL(TableB[Category]).

This new table would join the other two tables with category. 

I hope this helps.

I had tried using a bridge table but that didn't solve it for me.  (I still have many-many until the user selects a discount level.)

Hi Bill:

If you supply sample data it can be figured out. Are your discounts for each category static or rolling based upon time intervals.

E.G. of Configuration Table

Category   Discount %    Start          End 

Bikes             .10         1-01-2022     2-01-2022

Bike               .07          2-15-2022    2-28-2022

Biles              .05          3-01-2022 

Toys               .10          2-01-2022      4-15-2022  etc.   

etc.

If this is the case you'd need a separate config table to run logic thru. It's hard to guess without seeing some data and expected results. 

Hi:

 

I think you are talking about make a New Parameter. And the values in the new parameter are the discount amounts. (.05, .1, etc - whatever ones you have).

I will attach a file showing how to implement this. In the attachment a user can select the rate of expected sales growth (the parameter) and that value is used in a measure. Page three of this report shows the parameter "Sales2" and the parameter measure "Sales Value" is used in the final measure "Goal". This same approach will work for you based on discounts. If you go to Modeling>New Parameter you will see the screenshot below. Once you key in your discount values a new slicer wil appear (for your users) and then you tie your measures into this parameter SELECTEDVALUE.

It wouldn't hurt to watch a 3-5 minute Youtube on Power BI Desktop New Parameter for extra background. I hope this input solves your question. You will notice there is a data model behind the scenes suppporting the analysis.

https://drive.google.com/file/d/1VfghfltY9bseg0W-x4pyJeNLTXau-MCs/view?usp=sharing 

 

If this still sounds fuzzy, please post some example data for your tables and I will attempt to do it for you.

Thanks

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