cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Re: show items with no data as 0 instead of blanks

Do you have a copy of you DAX formula, by chance?

 

It sounds like you could do a count against the day column and use that as your denominator.

Highlighted
Regular Visitor

Re: show items with no data as 0 instead of blanks

I've had multiple connections and for some reason adding a zero made my pivot disconnect with everything else and eventually messing everything up... my logic is that when I added a fixed value which was not part of the datamodel and that's why my pivot disconnected from everything.

 

The solution (same same but different).... adding  + [var] - [var]... and the var should have some value, eg be a base value.

Highlighted
New Member

Re: show items with no data as 0 instead of blanks

Smartest and best answer!!

Highlighted
Frequent Visitor

Re: show items with no data as 0 instead of blanks

Very elegant and saved me a bunch of time!

Highlighted
Resolver I
Resolver I

Re: show items with no data as 0 instead of blanks

Hi, When i do this to my measure it does add a 0 to the measure, however when i have my matrix it expands the rows so it repeats 0's for rows that it shouldn't show. I assume i need to relate it somehow?

Highlighted
Advocate II
Advocate II

Re: show items with no data as 0 instead of blanks

@orangeatom What is your dax function? And have you a screenshot of the resulting matrix? 

 

Seems something like if the row total = 0 then return blank() for the row values, instead of zeros.

Highlighted
Resolver I
Resolver I

Re: show items with no data as 0 instead of blanks

I found the solution by approaching the problem in a different view. What worked for my is to correctly keep blanks at the lowest grain of my measure which I now realize was a similar yet different problem. My solution was to create a measure with a variable and a return that correctly keeps the 0's.

 

Measure Hide Blanks =
RETURN
IF(ISBLANK(Measure1),
 IF(ISBLANK(Measure2),
  IF(ISBLANK(Measure3),
   IF(ISBLANK(Measure4),
    Measure5,Measure4
   ),Measure3)
  ,Measure2)
 ,Measure1)

 

Highlighted
Helper II
Helper II

Re: show items with no data as 0 instead of blanks

Thanks!!

Highlighted
Anonymous
Not applicable

Re: show items with no data as 0 instead of blanks

Heyy, this works! Thanks!

For me it needs some final tweaking. I'm using a measure with a start and end date and I dont want it to show zeros before the start and after the zero, because I have 3 measures that follow up each other. See screenshot. 

 

Quantitydeal = CALCULATE (
SUM ('table'[Quantity]);
FILTER('tableinvoice';'tableinvoice'[Posting Date]>=MIN('Dealsperiod'[Startdate])&& 'tableinvoice'[Posting Date] <=MAX('table2'[Enddate])))+0
 
So where the first gray line stops, the blue begins and after the blue line the second grey line starts. I dont want it to stay zero before these date periods. Do you have a solution for my formula?
 
screenshot_deal.JPG
 
Highlighted
Anonymous
Not applicable

Re: show items with no data as 0 instead of blanks

Kinldy assist, im still getting a blank on mine:(

 

Here's the formula: 

No of Breakdowns completed in same month = if('Coal Lab'[Start Date].[Month]='Coal Lab'[End Date].[Month],1 ,0)
With +0:
if('Coal Lab'[Start Date].[Month]='Coal Lab'[End Date].[Month],1 ,0) +0

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors