cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

Frequent 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.

Occasional Visitor

Frequent Visitor

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

Very elegant and saved me a bunch of time!

Regular Visitor

## 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
Frequent Visitor

## 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.

Regular Visitor

## 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)

Regular Visitor

Thanks!!

Frequent Visitor

## 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?