cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajdsing Frequent Visitor
Frequent Visitor

How to change measure value using parameters?

I have 3 measures. I want to give user an option to select a measure to be showed in chart and the chart updates dynamically based on selection. Please help me to do this

 

For Ex. User would want to see a quantity or cost or weight in a chart at a time, on country level. I want to give a dropdown (select value of a parameter) which has above 3 options. Based on what he selects, the chart changes to show the measure selected?

1 ACCEPTED SOLUTION

Accepted Solutions
konstantinos Senior Member
Senior Member

Re: How to change measure value using parameters?

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

Konstantinos Ioannou
12 REPLIES 12
konstantinos Senior Member
Senior Member

Re: How to change measure value using parameters?

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

Konstantinos Ioannou
davekerby Regular Visitor
Regular Visitor

Re: How to change measure value using parameters?

What a great solution. Thanks for sharing this.

 

 

Re: How to change measure value using parameters?

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!

hmokkapati Regular Visitor
Regular Visitor

Re: How to change measure value using parameters?

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 

 

https://community.powerbi.com/t5/Desktop/Change-DAX-measure-format-based-on-Market-selection/m-p/117...

 

Thank you,

Harisha

Re: How to change measure value using parameters?

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!

hmokkapati Regular Visitor
Regular Visitor

Re: How to change measure value using parameters?

Hello brunozanoelo,

 

Agreed. In the mean time, I just decided to display a text measure that displays the Unit based on the selection below each chart.

 

Thank You,

Harisha

Ankita1310 Occasional Visitor
Occasional Visitor

Re: How to change measure value using parameters?

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! 

 

 

dcrios Frequent Visitor
Frequent Visitor

Re: How to change measure value using parameters?

Hello,

 

Its not working for me, it appear an error in switch level. Can you provide another solution for that? Thank you very much

dcrios Frequent Visitor
Frequent Visitor

Re: How to change measure value using parameters?

Hello,

 

Its not working for me, it appear an error in switch level. Can you provide another solution for that? Thank you very much

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 295 members 3,201 guests
Please welcome our newest community members: