Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Chrisjr
Helper IV
Helper IV

showing zero instead of blank in pivot table or bar chart

Hello All, 

 

I have created the below pivot table :

Chrisjr_0-1694684032507.png

 

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:

Chrisjr_1-1694684441478.png

 

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

 

How could I fix it ? 

 

Thanks

 

 

1 ACCEPTED SOLUTION
nirali_arora
Resolver II
Resolver II

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

Please provide sample data.

View solution in original post

8 REPLIES 8
sjoerdvn
Super User
Super User

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.

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

pawe84
New Member

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

 

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

 

hi @pawe84 , thanks, I tried it but doesnt work 

nirali_arora
Resolver II
Resolver II

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

Please provide sample data.

 

So fact table would be as below:

Column(to count)BrandID
aaa1
bbbb2
zzz3
ee4
rrz5
ete6
zr7
er8

 

The dim table would be as below:

segmentBrandBrandID
Bus&CoachVOLVO1
COMMERCIALVOLVO2
HEAVYVOLVO3
MEDIUMVOLVO4
Bus&CoachVOLKSWAGEN5
COMMERCIALVOLKSWAGEN6
LIGHTVOLKSWAGEN7
Bus&CoachVDL8
nirali_arora
Resolver II
Resolver II

You can try this  count(column)+0

thanks @nirali_arora but it didnt work, still showing blanks 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors