Tried all sorts of DAX formulas -- cannot turn blanks to 0 on my matrix (image included)

[Zarlot531]

I've searched through scores of people with problem and have tried adding zero, if formulas, ISBLANK, etc. and nothing seems to want to bring back a 0 where I'm getting blank cells. I need the row average to pick up blank cells and treat them as zero so that the average is accurate. It's not a huge deal at such a granular level but just would like to get it right. Image included with fake data. thanks. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file and show the expected result there.

[Zarlot531]

I changed this a little, removed all measures and removed the SQL link and imported it from a spreadsheet instead. But the basic data structure is the same. 

https://www.dropbox.com/s/qvjlkrn1vz5knvb/TicketProgressionDummyData.pbix?dl=0

@Anonymous @jdbuchanan71 

[jdbuchanan71 · Accepted solution]

Hello @Zarlot531 

By adding a date table and measures I was able to get this to work.  In your data you only had information on Sundays so my measure does a count over the dates in the fact table to decide where to do the zero fill.  Without this it puts zeros on every day of the week.  If you have data for every day, not just Sundays, you don't need this part.

The date table allows the DAX engine to have a value that exists (the date) separate from the values for Created, Resolved, Open so that gives us something to attach the 0 to.

My updated sample file with the date table and measures: https://www.dropbox.com/s/gh2f0zvzdombjmg/TicketProgressionDummyData.pbix?dl=0

Hope this helps.

[Zarlot531]

Thanks! Yeah, I sum up all the weekly activity at the server level in an SQL Statement, so there was no need to bring in all the other dates originally, only week-end dates. But yeah this makes sense on some level to me. 

[jdbuchanan71]

If you do it in SQL, you could pull another query of just the dates you are reporting on.  Then you join that single column table to your fact table and the result would be the same.  You pull in the date for your columns from the SQL list of Sundays table.  We just need a seperate table to hang the 0 on.  That would also mean you wouldn't need the row counting I did in the measures to avoid putting 0 on the other days of the week because they wouldn't exists.

[Anonymous]

Hey,

I do know why ISBLANK etc is not working, however i do not have a fix. The problem is that those values arent blank, they arent null, they are just not in the data, thus not showing. A fix would be to add 0's to them somehow. (maybe on the SQL side).

Goodluck!

[jdbuchanan71]

Hello @Zarlot531 ,

Can you give this a try for me?

OpenAvg = 
VAR OpenAmt = IF ( ISBLANK ( SUM ( Query5[Open] ) ), 0, SUM ( Query5[Open] ) )
VAR DistinctDates = IF ( ISBLANK ( DISTINCTCOUNT ( Query5[Date] ) ), 0, DISTINCTCOUNT ( Query5[Date] ) )
RETURN DIVIDE ( OpenAmt, DistinctDates, 0)

[Zarlot531]

That didn't work either, but thank you for trying. I'm beginning to believe nothing can solve this problem without extensive re-work. 

I tried this just recently and it didn't work either: 

OpenAvg = Divide(sumx(Query5,[Open]),DISTINCTCOUNT(Query5[Date]),0)

[Anonymous]

Could you please upload the file with fake data?