cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

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

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

6 REPLIES 6
Continued Contributor
Continued Contributor

@Lakshmi_Koduri 

 

Do you have picture for reference?

Super User I
Super User I

@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.

DataZoe_0-1600481070958.png

 

 

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.

Super User II
Super User II

@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?

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Super User II
Super User II

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.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

 

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:

Lakshmi_Koduri_1-1600558343879.png

 

 

@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? 

AllisonKennedy_0-1600602022663.png

 

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? 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors