topic Re: My two tables are many-many. How to make one-many with a slicer? in DAX Commands and Tips

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

Bill_NYC — Tue, 26 Apr 2022 19:14:32 GMT

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.)

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

Whitewater100 — Tue, 26 Apr 2022 20:23:23 GMT

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.

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

Bill_NYC — Wed, 27 Apr 2022 12:15:25 GMT

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.)

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

Whitewater100 — Wed, 27 Apr 2022 12:48:40 GMT

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

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

Bill_NYC — Wed, 27 Apr 2022 16:01:26 GMT

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

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

Whitewater100 — Wed, 27 Apr 2022 17:47:57 GMT

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.

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

v-easonf-msft — Fri, 29 Apr 2022 09:49:45 GMT

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