cancel
Showing results for
Did you mean:
Highlighted
Regular 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?

Regular 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!!

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

Frequent Visitor

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 57 members 1,069 guests
Recent signins: