I have a DAX Measure with a Switch True Statement that retrieves some number values that must be displayed as general numbers and some as percentages. I want to display these values in a Line and Column Chart.
I’ve used the FORMAT() function around percentage values but the chart shows nothing. I think it is because the format function is turning the percentage values into a String.
How do I display these two different types of values appropriately in the Line and Column Chart, when either one or the other is retrieved by the Switch Statement?
Sample3_Revised.zip (574.3 KB)
Is it possible to restructure the data in your table? Here is what table looks like after I adjusted it:
The I changed the format of General Number to “Whole Number” and Percentage to “%”
Here is what it looks like in the chart afterwards without having to use a measure:
Hi OJones,
My apologies, this is just my attempt to replicate the problem in a simple way but I should not have used a 1000% as an example because I think that took the focus off the real problem of the FORMAT() function causing the percentage value to not display in the chart. The values in this sample query are just random but in reality, it would be a Measure with valid values.
The essential parts are the Slicer, MyMeasure and the Chart. The Table visual is just to show that the FORMAT() function works in that visual.
When a General number option is selected from the Slicer, the chart needs to show the value on the chart as a number but when the Percentage option is selected from the Slicer, the chart needs to show that value on the chart as a percentage.
There has to be another way? I considered using a bookmark to alternate the visuals, but I don’t think that can work with a Slicer??
I still think your best bet is to have 2 different columns. Are those columns of data, or are you using a measure to get the numbers? If so, have 2 measures, 1 being whole number, and then other being the %. You can then use the “MyMeasure” that you have, but you would need to adjust your SWITCH statement to choose the appropriate measure for each SelectedItem.
Thanks
1 Like
Thank you, OJones!
The 2 measures idea was my initial instinct, but I was hoping for a different option because the number of measures would double when applied to many instances like this.
If there’s no other ideas for this, then I’ll have to double-up the measures, but more ideas are very welcome!
I’ll definitely give it some more thought, but I think this is your best route for now.
Thanks
1 Like