topic Re: showing zero instead of blank in pivot table or bar chart in DAX Commands and Tips

showing zero instead of blank in pivot table or bar chart

Chrisjr — Thu, 14 Sep 2023 09:46:30 GMT

Hello All,

I have created the below pivot table :

I need to replace the blank values by 0. The Segment column and the Brand column both come from the same Dim Table.

The mesure is a count of of a column in the fact table. Some of the brands don't have a relationship with all the segments.

I need it to show as 0 because when I transform the pivot table in a bar chart, all the segment still need to show on the bar chart and not disapear when it is blank. For example, if I select Automet on a filter, then the bar chart will only show the bus&coach segment. Instead, I still need the bar chart to show me the other segment with no values like below:

I have tried IF(ISBLANK(count(column)), 0, count(column))

How could I fix it ?

Thanks

Re: showing zero instead of blank in pivot table or bar chart

nirali_arora — Thu, 14 Sep 2023 10:01:18 GMT

You can try this count(column)+0

Re: showing zero instead of blank in pivot table or bar chart

Chrisjr — Thu, 14 Sep 2023 10:06:34 GMT

thanks nirali_arora but it didnt work, still showing blanks

Re: showing zero instead of blank in pivot table or bar chart

nirali_arora — Thu, 14 Sep 2023 10:11:28 GMT

It works better when rows and columns are coming from separate dimension/table

Please provide sample data.

Re: showing zero instead of blank in pivot table or bar chart

pawe84 — Thu, 14 Sep 2023 10:57:38 GMT

Did you try it inside your transformation table with the folowing DAX statement?

modified_column = IF ( LEN ( column[Name] ) = 0, 0,column[Name] )

Re: showing zero instead of blank in pivot table or bar chart

Chrisjr — Thu, 14 Sep 2023 10:59:59 GMT

So fact table would be as below:

Column(to count)	BrandID
aaa	1
bbbb	2
zzz	3
ee	4
rrz	5
ete	6
zr	7
er	8

The dim table would be as below:

segment	Brand	BrandID
Bus&Coach	VOLVO	1
COMMERCIAL	VOLVO	2
HEAVY	VOLVO	3
MEDIUM	VOLVO	4
Bus&Coach	VOLKSWAGEN	5
COMMERCIAL	VOLKSWAGEN	6
LIGHT	VOLKSWAGEN	7
Bus&Coach	VDL	8

Re: showing zero instead of blank in pivot table or bar chart

Chrisjr — Thu, 14 Sep 2023 11:08:32 GMT

hi pawe84 , thanks, I tried it but doesnt work

Re: showing zero instead of blank in pivot table or bar chart

sjoerdvn — Fri, 15 Sep 2023 11:54:06 GMT

Previous posters already hinted at this, but if the "+ 0" doesn't work, then the issue is with your dimension table not containing the Segment/Brand combinations you want to show zeros for. This can't be solved with a dax measure.
So either make sure the dimension table contains these combinations or otherwise create an additional dimension table and link that to the fact.

Re: showing zero instead of blank in pivot table or bar chart

Chrisjr — Mon, 18 Sep 2023 06:58:31 GMT

sjoerdvn thanks for the help. They do contain the combinations. I think I will just create a separte dimensions.