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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors