CODEWITHSUNDEEP
powerbi
ballade4
ballade4

Reputation: 89

Is it possible to use a button or slicer to select which measure appears in Values of a visualization?

In short, I would like to have a matrix that is specific to one Value over time (ex. sales per month), with ability for a user to replace sales with units or margin by selecting a button or slicer. I can conceive of a arse-backwards way to do this such as a complicated IF-based formula and data to support, but this seems like something that could be stock functionality that is presently outside of my knowledge base. Thank you in advance for any and all input on this!

Upvotes: 0

Views: 759

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Yes. Define your different measures like [sales], [units], and [margin] and then write a measure that switches between them based on what [metric] you've chosen with your button/slicer.

SwitchValue =
VAR SelectedMetric = SELECTEDVALUE ( Metrics[metric] )
RETURN
    SWITCH (
        SelectedMetric,
        "Sales", [sales],
        "Units", [units],
        "Margin", [margin]
    )

This assumes you've created a table Metrics like this to use for your slicer:

metric
------
Sales
Units
Margin

If you want to format these separate measures differently, one workaround is to wrap each one in a FORMAT function specifying your desired format (either pre-defined or custom). For example,

SWITCH(SelectedMetric,
    "Sales",  FORMAT([sales], "Currency"),
    "Units",  FORMAT([units], "0"),
    "Margin", FORMAT([margin], "0.00%")
)

The disadvantage of this is that these are all now text values (strings), which can make sorting behave not as expected. Also, if you choose to export your data, you'll have lost any precision beyond the decimal places you've formatted to.

Upvotes: 1

Related Questions