cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sphillips22 Regular Visitor
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.

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

sophyw Occasional Visitor
Occasional Visitor

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

Smartest and best answer!!

SingleT Frequent Visitor
Frequent Visitor

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

Very elegant and saved me a bunch of time!

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

sphillips22 Regular Visitor
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.

orangeatom Regular Visitor
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)

 

ritanoori Regular Visitor
Regular Visitor

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

Thanks!!

Rienster Regular Visitor
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?
 
screenshot_deal.JPG
 
Mlekhule Frequent Visitor
Frequent Visitor

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

Kinldy assist, im still getting a blank on mineSmiley Sad

 

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
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 57 members 1,069 guests
Please welcome our newest community members: