Dynamic Measure Selection
Problem #1:
You want the ability to change the currency in a visual that displays company sales with a toggle.
Solution:
1) Create a table that consists of each currency value. You can use an ID column if you have a preferred ordering that is not alphabetical.
2) Create a measure for the sales value in each currency.
Sales (USD) = SUM('Sales Table'[Sales Amount)3) Create a slicer visual that contains each currency option from the table you created.
4) Write a measure that changes the value it displays based on the currency slicer selection
Sales (Dynamic Currency) =
SWITCH( TRUE(),
VALUES('Dynamic Currency'[Currency]) = "CAD", [Sales (CAD)],
VALUES('Dynamic Currency'[Currency]) = "USD", [Sales (USD)],
BLANK())Final Result:
Problem #2:
You want to have a toggle but rather than currency you want to be able to flip between different metrics (Dollars, Units, and Gross Margin for example).
Solution:
1) Follow the same steps as above by creating a table that contains each metric you want to be able to toggle between.
2) Write the measure that changes the metric being displayed based on a slicer selection.
Dynamic Metric =
SWITCH(
TRUE(),
SELECTEDVALUE( 'Dynamic Metric'[Metric] ) = "Dollars", [Sales],
SELECTEDVALUE( 'Dynamic Metric'[Metric] ) = "Units", [Units],
SELECTEDVALUE( 'Dynamic Metric'[Metric] ) = "Gross Margin", [Gross Margin]
)
3) Create your slicer and visual.
Important Note: The downside to this solution is that since it is a single measure it can only be formatted one way. Meaning, that if you format it as currency so that you have the "$" in front of the value, you waill also have a "$" in from of Units and Gross Margin. You can get around this by wrapping each of the individual measures within the Dynamic Metric measure with a FORMAT function and then having a custom format for each measure. However, this formats the entire measure as text which makes it only usable in select visuals and you cannot sort numerically anymore. Fortunately, there is a new solution for this!
In the measure formatting options you can now choose "Dynamic" as an option.
When you do this, the formula bar will update with a new dropdown that allows you to toggle between the measure formula and the formatting formula.
The Format formula will show whatever your current selected formatting is. In my case, it was "General". You can now write a DAX formula that formats each metric individually (you do not need to title it like you would a measure).
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Dynamic Metric'[Metric] ) = "Dollars", "$,0",
SELECTEDVALUE( 'Dynamic Metric'[Metric] ) = "Units", "#,0",
SELECTEDVALUE( 'Dynamic Metric'[Metric] ) = "Gross Margin", "#.##%"
)
Now your measure will update the formatting dynamically as seen in these images:
