Forum Discussion
DAX FORMAT value based on slicer selection
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!
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.
10 Replies
This sounds like an ideal scenario for Calculation Groups. Have you considered that option?
Here's a good article to get you started
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.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 😊
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
You can now use Field Parameters for that, and swap out the measure as needed.
