Forum Discussion
How to change measure value using parameters?
- 10 years ago
rajdsing You cannot have a dropdown but you can use slicers or filters to change the calculations on chart.
You need to create a table that hols the values which you want to show in the slicer
Calculation Name SortID Cost 1 Quantity 2 Weight 3
The SortID is there for sorting the values in slicer as also use it to change the calculation.
The calculations are fictional, change it to your own measures. No relantionship is needed betwwen the calculation name table and any other.
The measure can be like this
Variable Calculation = VAR Costs = SUM ( Sales[Cost] ) //Change with your measure VAR Quantitys = SUM ( Sales[Quantity] ) //Change with your measure VAR Weights = SUM ( Sales[Weight] ) //Change with your measure VAR SelectMeasure = MIN ( CalculationTable[SortID] ) RETURN IF ( HASONEVALUE ( CalculationTable[Calculation Name] ); SWITCH ( SelectMeasure; 1; Costs; 2; Quantitys; 3; Weights ) )Add the [Variable Calculation] measure to any graph and add as filter of slicer the values from the table Calculation Names.
This should work
*Note that you cannot change the format based on selection ( at least not I am aware of )
rajdsing You cannot have a dropdown but you can use slicers or filters to change the calculations on chart.
You need to create a table that hols the values which you want to show in the slicer
Calculation Name SortID Cost 1 Quantity 2 Weight 3
The SortID is there for sorting the values in slicer as also use it to change the calculation.
The calculations are fictional, change it to your own measures. No relantionship is needed betwwen the calculation name table and any other.
The measure can be like this
Variable Calculation =
VAR Costs =
SUM ( Sales[Cost] ) //Change with your measure
VAR Quantitys =
SUM ( Sales[Quantity] ) //Change with your measure
VAR Weights =
SUM ( Sales[Weight] ) //Change with your measure
VAR SelectMeasure =
MIN ( CalculationTable[SortID] )
RETURN
IF (
HASONEVALUE ( CalculationTable[Calculation Name] );
SWITCH ( SelectMeasure;
1; Costs;
2; Quantitys;
3; Weights
)
)
Add the [Variable Calculation] measure to any graph and add as filter of slicer the values from the table Calculation Names.
This should work
*Note that you cannot change the format based on selection ( at least not I am aware of )
- davekerby10 years agoHelper II
What a great solution. Thanks for sharing this.
- brunozanoelo9 years agoPost Patron
And how can i format those values in the DAX return?
Example:
50
IF the choice is Price, the value must be seen with brazilian coin format (R$ 50,00).
IF the choice is Quantity, the value must be seen like 50,00
IF the choice is weight, the value must be seen like 50,0000
How can i do that?
Thank's for all!- hmokkapati9 years agoHelper II
Hello,
Any idea how the format can be changed?
I have a requirement to change the format of price based on the selection of the Country. Please advise. Also posted the question here
Thank you,
Harisha
- brunozanoelo9 years agoPost Patron
Hi hmokkapati, the problem with the different values is solved BUT the different format type still dont havea solution.
I think we could create a ticket to power bi team, because, tableau and differents Business intelligence tools already have this feature.
Any update i will post here.
Thanks!
- Ankita13109 years agoNew Member
Thank you for the solution, it was very helpful.
I was wondering if we can do the same for columns(dimensions) instead of measures. i.e. if I want Power BI to group my numeric values for different columns based on a selection (for a bar chart)
For example: for a heatmap, I want to toggle between Product, Customer,State to show the sales amount by Product or by Customer or by State based on the dimesnion selection. I know we can achieve that using the arrow down after creating a hierarchy in the Group , but I was wondering how to do that using a slicer, making it more user friendly.
Thanks for the help in advance!
- dcrios8 years agoFrequent Visitor
Hello,
Its not working for me, it appear an error in switch level. Can you provide another solution for that? Thank you very much
- dcrios8 years agoFrequent Visitor
Hello,
Its not working for me, it appear an error in switch level. Can you provide another solution for that? Thank you very much
- jbrosh238 years agoFrequent Visitor
dcriosTry replacing the semicolons with commas. That worked for me. Thanks for sharing this solution konstantinos!
- dcrios8 years agoFrequent Visitor
Thank you!
- Yamuna7 years agoFrequent Visitor
Hi ,
I am also looking for the same solution.can you please provide the sample workbook?
Thanks in Advance
Yamuna