Show 0 value on Line Chart in Power BI for missing data

Question

I have a table with sparse values, but in my line chart I'd like the line to go back to zero when there's no data to show. e.g:

instead of:

I want:

To produce the first graph I used a generated table with a series of values for the x-axis, and the sparse data table was joined onto that generated table to provide the values.

In some cases there's a summation of the values, which show with the line above zero. The values that appear low down to the axis are in fact just very small values from my data, they are not zeros.

Selecting "Show items with no data" on the axis doesn't seem to do what I want, which other answers I've found are suggesting. Is there something I'm missing?

I would like to find out what options I need to use, or if there's some kind of trick with the data table I should implement, which would give me zeros on the line graph (as shown in the second image) when there is no data found.

Answer 1

Ryan provides a great answer for handling this in the Power Query editor. If you (or someone with your same question) can't edit the queries, here is a couple of options for solving this issue in DAX and visual settings.

One thing I want to note about Ryan's answer is "null" and "zero" are drastically different items. A "null" means no data and a "zero" means 0. Depending on what kind of data you're looking at, treating those as the same could lead to misinformation.

---

Solutions:

1. If your X axis is "Categorical", you can use the "Show items with no data". At which point any value on your X axis that has no data will be shown as a gap in the line.

   This would be my preferred method to show items with no data.

2. If your X axis is "Continuous" and/or you really want to show zeros, simply create a measure with the following formula to turn all blanks into zeros.

This is what the line chart would look like with a normal measure.

And this is what it looks like using the new measure.

And here is the measure formula that gives the behavior in the last picture.

    Total Amount = IF(ISBLANK(SUM(Table1[Amount])), 0, SUM(Table1[Amount]))