DAX FORMAT value based on slicer selection

[Anonymous]

Hi -

I have created three measures: Net Sales per Pound, Volume, and Mix. I want to show each of these measures in a matrix, but only show one measure at a time based on which one is selected in a slicer. I created a second table so that when "Net Sales/Lb" is selected, the value for the measure "NS per Lb." will be returned. When "Volume" is selected, the value for the measure "Volume" will be returned. When "Mix" is selected, the value for the measure "Percent" with be returned. 

Now, I just need to format these numbers correctly. 

• Net Sales/Lb will be formatted in USD: $0.00;($0.00);0
• Volume will be formatted as a decimal number: 0.00;(0.00);0
• Mix will be formatted as a percent: 0.00%;(0.00%);0

I am having trouble with the DAX of this. Below is what I have tried, and what is returned are blanks in the visual.

Measure Selection = 
VAR Slicer = SWITCH(TRUE(),
VALUES('Measures Table'[Measure]) = "Net Sales/Lb", 'Sales & Volume Data'[NS per Lb.],
VALUES('Measures Table'[Measure]) = "Volume", 'Sales & Volume Data'[Volume],
VALUES('Measures Table'[Measure]) = "Mix", 'Sales & Volume Data'[Percent],
BLANK())
RETURN
FORMAT(Slicer,
SWITCH(
 TRUE(),
 VALUES('Measures Table'[Measure]) = "Net Sales/Lb", "$0.00;($0.00);0",
 VALUES('Measures Table'[Measure]) = "Volume", "0.00;(0.00);0",
 VALUES('Measures Table'[Measure]) = "Mix", "0.00%;(0.00%);0")
)

I also tried this measure:

Measure Selection = 
SWITCH(TRUE(),
VALUES('Measures Table'[Measure]) = "Net Sales/Lb", FORMAT('Sales & Volume Data'[NS per Lb.],"$0.00;($0.00);0"),
VALUES('Measures Table'[Measure]) = "Volume", FORMAT('Sales & Volume Data'[Volume],"0.00;(0.00);0"),
VALUES('Measures Table'[Measure]) = "Mix", FORMAT('Sales & Volume Data'[Percent], "0.00%;(0.00%);0"),
BLANK())

Both return a table of blank values. Any ideas on how to fix? All help is appreciated! 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Maybe you can optimize the code a little bit as follows, but  I've tested your code and it works. Maybe your data itself or all three measures check to see if they can all return numeric values. You can follow my simple example：

MeasureSelection = 
SWITCH(VALUES('Measures Table'[Measure]), 
 "Net Sales/Lb", FORMAT('Sales & Volume Data'[NS per Lb.],"$0.00;($0.00);0 "), 
 "Volume", FORMAT('Sales & Volume Data'[Volume],"0.00;(0.00);0"), 
 "Mix", FORMAT('Sales & Volume Data'[Percent], "0.00%;(0.00%);0"), 
BLANK())

The final output is shown below:

There is another possibility that three measures are applied to the matrix, which shows blank space due to row level reasons. Could you check it? If it is not solved, can you share simple files or screenshots to remove sensitive information?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DileepRajam]

This works for table visual but if i am trying to add this measure to a Column Clustered Bar chart, it is not letting me add the measure to my Y-Axis. Is there a way to display the values of bar chart as % or Number based on slicer selection

[lbendlin]

You can now use Field Parameters for that, and swap out the measure as needed.

[DileepRajam]

Hi @lbendlin thank you for the quick reply

My requirement is to show workload performed at each facility (%/hr or min/hr) based on shifts (Day, Night,Weekends and Total). I have already created one Field Parameters with 4 measures to get the workload based on shifts and using that slicer in my chart to swap the measures. 

Can you help me how can I map another Field parameters (with 2 new measures) that swap between %/hr and min/hr which acts as an independent slicer from the Shifts slicer

below is screenshot for your reference of what our desired output is

Thanks,

-Dileep

[lbendlin]

Change the field parameter to include all 8 combinations, or use separate visuals.

[Anonymous]

This is still returning blanks in my matrix, unfortunately. If I take out the formatting parts and have this simple measure, proper values are shown: (formatted incorrectly of course)

MeasureSelection = 
SWITCH(VALUES('Measures Table'[Measure]), 
 "Net Sales/Lb",'Sales & Volume Data'[NS per Lb.], 
 "Volume",'Sales & Volume Data'[Volume], 
 "Mix",'Sales & Volume Data'[Percent], 
BLANK())

I created a sample workbook that I could share here. I do not have access to the original data, so I was inputting random, insensitive information. I attempted the measure you provided, and it works perfectly. It must be an issue with our data.

Thank you for the help 😊

[Anonymous]

Still having trouble. Any other ideas?

[lbendlin]

This sounds like an ideal scenario for Calculation Groups. Have you considered that option?

[Anonymous]

I have not. Could you please elaborate? 

[lbendlin]

Here's a good article to get you started

Controlling Format Strings in Calculation Groups - SQLBI