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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 487 members 4,466 guests
Please welcome our newest community members: