How to remove the blank values from a clustered column chart?

[Lakshmi_Koduri]

Hi,

I have a clustered column chart with values displaying per quarter. There are few blank values and the bars are shown up even for the blank values like empty spaces. I tried all the ways to get rid of the blank values like setting the visual level filters set not to be blank, through DAX ( if(value=blank(),0,value). But none of the ways are working. Still I am not able to get rid of blank spaces.

Is there anyone who has seen such issue and able to remove the blank spaces between the bars? If so, please help me with the solution>

Thanks 

Lakshmi Koduri

[AllisonKennedy]

Continuing my reply, if the blanks show up on the bars, this post might be more helpful to explain why: https://excelwithallison.blogspot.com/2020/07/how-to-handle-blank-measures.html
But Zoe's solution should help in that case.

[AllisonKennedy]

@DataZoe has given a great solution.
If that doesn't work, you may also need to clean up your data.

A picture really would help us troubleshoot this for you faster. Are the blank values showing up on the axis or in the bar value?

If they show up on the axis see if this post and these suggestions might help:
https://excelwithallison.blogspot.com/2020/07/what-does-this-blank-mean.html
Is it possible there are mismatch rows between your date and fact tables? Or white spaces in your data?

[DataZoe]

@Lakshmi_Koduri 

Based on what you said, I would first check these things:

1. If you click the drop down menu by quarter, make sure the "Show items with no data" is un-checked.

2. Make sure axis is categorical not continuous. 

3. That measure would actually make it show. 🙂 The measure would want to be blank() for the chart to skip that quarter, not 0.

[Lakshmi_Koduri]

Hi,

So based on your approach, I made sure that the "Show items with no data" is unchecked, x-axis is categorial and changed my measure to be blank() and then applied a visual level filter for my values as "Is not blank". Still I could see the blank spaces for the bar with no data. I could not identify for what reason blank() condition is not getting applied to the measure values and why I am not able to filter out those values in the graph. A ref pic is below:

[AllisonKennedy]

@Lakshmi_Koduri Thanks for the picture - it helps a lot to see the problem.

Because you have put Year in the Legend field, it will keep the blank spaces for consistency. 

Do you like any of the alternative options below? 

Top left is using custom marketplace visual by Akvelon with "Column by" Quarter and under Format: "X-Axis > Axis Range Type" set to "Separate". Bottom left changes chart type to stacked column. Bottom right puts both quarter and year in Axis, expands down a level and under Format > Data Colors > Show All > On and customize the colors as you want. 

None of them are exactly what you're looking for, but hopefully helps a bit? 

[darentengmfs]

@Lakshmi_Koduri 

Do you have picture for reference?