Keeping a clustered bar chart's axes constant when there's no data

[kimchizal]

I have a clustered bar chart that changes itself when there's no data present. There are two things happening, and if I fix the first, I think that would resolve the second.

1 - When I update filters and no data ends up being displayed, all the y-axis labels disappear. I've already set the option to "Show items with no data" but that doesn't appear to resolve the issue. Do I need to make another reference/dimension table that would store my y-axis labels and display them at all times?

2 - When no data for any of the labels is displayed, the visual adjusts itself since there aren't any labels to display either. So my x-axis becomes larger and adds in more granular tick marks, which I don't want. I've tried setting the x-axis' "Value decimal places" settings to 0, but that makes it look weirder with 11223344. If I can resolve issue #1, this probably won't be an issue either, but in the event that I can't figure out how to do #1, how do I resolve this on its own?

Visual with data before any filtering:

And this is what the visual looks like with filters so that no data ends up being displayed:

[v-juanli-msft · Accepted solution]

Hi @kimchizal

I can reproduce your problem.

To solve this, i create a measure

Measure = IF(MAX(Sheet2[value])=BLANK(),0,MAX(Sheet2[value]))

It this is not your expected output, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kimchizal]

Hmm I'm getting odd results. The field is supposed to be averaged across each category, and the MAX function is throwing off the calculation. Changing your formula to use AVG instead of MAX doesn't seem to work:

Display Measure = IF(AVG(Table[Value])=BLANK(),0, AVG(Table[Value]))

[kimchizal]

Nevermind, looks like AVG isn't a DAX function.... Using Average fixes it. Thanks Maggie!

[v-juanli-msft]

Hi @kimchizal

Could you show an sample dataset?

no data refers to values in  X-axis or values in  Y-axis?

Best Regards

Maggie

[kimchizal]

I'll work on getting a sample dataset. By no data, I mean no records. So after filtering, there aren't any records to show