cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Using a parameter in desktop

Hello everyone,

Hope someone can assist with my parameter issue which is causing a lot of headache.

I have two tables; (A) with three ID's and three products for each, and (B) which a discount at a given qty supplied:

 TABLE A ID Product Cost1 A X 48 A Y 50 A Z 24 B X 25 B Y 50 B Z 45 C X 31 C Y 46 C Z 35

 TABLE B ID Supplied qty Discount A 10 -3 A 20 -8 A 30 -20 A 40 -8 A 50 -15 A 60 -20 A 70 0 B 10 -14 B 20 -17 B 30 -16 B 40 -1 B 50 -7 B 60 -16 B 70 -4 C 10 -6 C 20 -5 C 30 -17 C 40 -14 C 50 -7 C 60 -3 C 70 -18

What I would like to do is to enable the report reader to adjust the supplied qty by using a parameter and then have a calculated table showing the new cost (2) i.e. if supplied qty is set to 10:

 Cost2 ID \ Product X Y Z A 45 47 21 B 11 36 31 C 25 40 29

and if set to 50:

 Cost2 ID \ Product X Y Z A 33 35 9 B 18 43 38 C 24 39 28

I have created a parameter in Power BI (GENERATESERIES(10; 70; 10)) which can be used to show the discount for each ID according to the given qty in the parameter in a table. However, I can't figure out how to calculate the new cost as shown above.

Thanks

1 ACCEPTED SOLUTION
Super User

@Top006 , You need have common table ID

measure =
var _value = selectedvalue(series[value])
return
sum(TableA [Cost]), calculate(Sum(TableB[Discount]), filter(TableB, TableB[ID] = Max(ID[ID]) && TableB[Supplied qty] =_value))

Bridge Table https://youtu.be/Bkf35Roman8

2 REPLIES 2
Super User

@Top006 , You need have common table ID

measure =
var _value = selectedvalue(series[value])
return
sum(TableA [Cost]), calculate(Sum(TableB[Discount]), filter(TableB, TableB[ID] = Max(ID[ID]) && TableB[Supplied qty] =_value))

Bridge Table https://youtu.be/Bkf35Roman8

Frequent Visitor

Thank you very much @amitchandak !!! This works excatly as intended..

Much appreciated 🙂

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.