Forum Discussion
How to change values based on the slicer selection using DAX
| Product | Date | QuantityDemand |
| A | 1/05/2019 | 58 |
| A | 18/03/2019 | 24 |
| B | 2/04/2020 | 52 |
| C | 7/05/2021 | 63 |
| C | 2/05/2021 | 100 |
| A | 3/10/2020 | 63 |
I have a data set that looks like the above and I want to create a what-if scenario and change the values of quantity demand based on the Product selection of slicer. For instance, if someone selects A, the Quantity demand for Product A must be set to zero and calculate the total Quantity demand. This is to see what would the total demand quantity be if the demand for a specific product or products is zero. I want to create a measure that changes the value of quantity demand and return the Total Quantity Demand dynamically.
Here is my trial below, but no luck yet.
Deletion =
var selection =
SELECTEDVALUE(Table[Product])
Return
SWITCH(TRUE(),
selection = HASONEVALUE(Data[Product])
,VALUES(Data[QuantityDemand])=0)
Can anyone please help me with this?
Hi Anonymous ,
You can use what-if parameter.
Then create the following separate table.
Table 2 = DISTINCT('Table'[Product])At last, create the following measure.
Total Quantity Demand = CALCULATE(SUM('Table'[QuantityDemand]),FILTER(ALL('Table'),[Product]<>SELECTEDVALUE('Table 2'[Product])))+SELECTEDVALUE('Quantity demand'[Dynamic Quantity demand])Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
You can use what-if parameter.
Then create the following separate table.
Table 2 = DISTINCT('Table'[Product])At last, create the following measure.
Total Quantity Demand = CALCULATE(SUM('Table'[QuantityDemand]),FILTER(ALL('Table'),[Product]<>SELECTEDVALUE('Table 2'[Product])))+SELECTEDVALUE('Quantity demand'[Dynamic Quantity demand])Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous there seems to be an error?
Quantities w/o dynamic slicer do not fit.
Sum of A = 145 (not 215)
Sum of B = 52 (not 308)
Sum of C = 163 (not 197)
Is there an issue with the measure?
- amitchandak
Super User
Anonymous , can you share expected output
like quantity other than selected
calculate(sum(Data[QuantityDemand]), filter( Data, not(data[Product] in allselected(data[Product] ))))
You can control this better if you have an independent table for the product
example Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE
