Show all categories even if there is no data

I have a powerpivot table which groups customers into ranges of sales figures. When I use a slicer to slice by region, I lose some of the ranges because they contain no data. I need them to show up even if there is no data. I've checked all of the boxes to "Show items with no data on columns (and Rows). But this has no effect. I created a measure using the following formula which also has no effect.

NeverBlankAcct:=IF(ISBLANK(Count([CACCT])),0,Count([CACCT]))

I have examples of the tables, but this forum wont let me post them (keep getting internal server error). I've searched everywhere and cannot find a solution that will work for me. Any help greatly appreciated.

July 22nd, 2015 3:08pm

Hi,

how about the slicer settings - did you check them as well? There is an option "Hide elements with no data" which is set off by default - but worth checking...

Free Windows Admin Tool Kit Click here and download it now
July 22nd, 2015 3:59pm

I believe you want ADDMISSINGITEMS,

https://msdn.microsoft.com/en-us/library/dn802537.aspx

July 22nd, 2015 10:23pm

Hi Domtrump,

According to your description, you are experiencing the losing some of the ranges because they contain no data issue when use a slicer to slice by region, right?

By default, Excel hides rows with blank values if there is no data for that ranges in your fact table. To workaround this issue, we can play with DAX and force a zero instead of a blank whenever no data exists in the fact table but you want the row to appear in the PivotTable. You need to check if COUNTROWS(ranges)>0 or not. Here is a similar issue with yours, please refer to the link below.
http://www.mrexcel.com/forum/power-bi/669878-show-items-no-data-rows-slicers-functionality-question.html

Regards,

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 2:07am

I believe you want ADDMISSINGITEMS,

https://msdn.microsoft.com/en-us/library/dn802537.aspx


It looks like this is a promising feature of a future release of Excel, but we are using Excel 2010 (and will be for quite some time). Thanks for the heads up, though.
July 23rd, 2015 9:49am

Hi Domtrump,

According to your description, you are experiencing the losing some of the ranges because they contain no data issue when use a slicer to slice by region, right?

By default, Excel hides rows with blank values if there is no data for that ranges in your fact table. To workaround this issue, we can play with DAX and force a zero instead of a blank whenever no data exists in the fact table but you want the row to appear in the PivotTable. You need to check if COUNTROWS(ranges)>0 or not. Here is a similar issue with yours, please refer to the link below.
http://www.mrexcel.com/forum/power-bi/669878-show-items-no-data-rows-slicers-functionality-question.html

Re

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 9:55am

OK, so to better understand this, would this represent your data:

  • Customer, Region, Sales, Range
  • CustomerA, Region1, $10, $0-$100
  • CustomerB, Region1, $150, $100-$1000
  • CustomerC, Region2, $2000, $1000-$2000

In this case, selecting Region1 would make the $1000-$2000 range go away, correct? 

Are these two slicers that you have, one for Region and one for Range?

July 23rd, 2015 11:10am

Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
...need ALL categories to show up ALL the time...
http://www.mediafire.com/view/d9ui82n0phoq4qf/07_23_15a.xlsx

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 10:24pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics