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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.