Forum Discussion
Format causing measure to return blank.
I can't share the file but I will work on building a comparable file that has the same data model layout and see if I can reproduce the issue.
I just tested the histogram bucketing and formatted measure in a table (rather than just a bar chart with labels) and it works fine, so now I don't know why it's not working properly with the bar chart. I believe the issue is that FORMAT() returns a text value, which the bar chart doesn't know is actually textual representations of numbers.
I can't share the file but I will work on building a comparable file that has the same data model layout and see if I can reproduce the issue.
I just tested the histogram bucketing and formatted measure in a table (rather than just a bar chart with labels) and it works fine, so now I don't know why it's not working properly with the bar chart. I believe the issue is that FORMAT() returns a text value, which the bar chart doesn't know is actually textual representations of numbers.
Hi spotpuff
I've used something similar to this before and had a similar problem. the issue for me was that the "FORMAT" always returns a string in the given format and as most graphical visuals expect a number format it cannot display it.
mine was slightly different in that my return values weren't all percentages so I had to set mine to a decimal number and just multiple or divide to get the correct output. Yours all appear to be percentages that are returned so I would have thought you'd be able to format the measure as a percentage (or custom with the necessary format) and remove the format function from the DAX return statement and leave as Count Percentage, IE Percentage and Premium Percentage.
I hope this helps.
Dog
Sorry just noticed that yours isn't just percentages.
you're not kidding, I went round in circles on this one.
the only thing I had considered although never followed it through was to have a stacked bar graph that has two outputs one for percentage values and one for the numerical values.
depending on slicer selection would depend on which one had data in to show on the graph - I'm assuming that it would then allow you to format the measures independently.
If I give it a go I'll let you know.
Dog
Hi Dog,
My values are both general number and percentages; you'll notice there are two SWITCH() functions in the "selected measure" statement.
Right now I'm going to go with a histogram by value and a histogram by percentage, but this is an unfortunate limitation of DAX/Excel functions.I believe in Excel I solved this with VBA when the chart changes, but in Power BI it looks like two separate charts is how it will have to be.