Show 0 in Line Chart Instead of Joining Gap

[oliverblane]

Hi, I have some data that looks like the following:

I am trying to plot this data on a drill-down line chart like the following:

The aim is to be able to drill down from date, to hour. There are two issues:

1) There is no data for days 26/27, so the line chart joins the lines instead of showing 0. I would like the chart to show 0 for these values. I have tried setting an ISBLANK() check, but this does not solve the issue.

2) When I drill down to hour, the hours are not in order, and not all hours of the day show:

Is there anything I can do to solve these issues?

Sample File: Sample File.pbix

[Shri_Pooja]

Hi @oliverblane , try sorting your chart by X-axis - Ascending. You can find the option when clicked on the ellipses of the chart as below:

Did I answer your question?

If yes, please mark the solution as Accepted!

[Thejeswar]

Hi,

Check the data type of the Hour Column. This sorting issue could be because the Hour column is in a text datatype. Change it to number and it should work.

Also there is a property in X-Axis that allows you to change the axis type. Set that to Categorical. That should display all the axis values. Below SS for your reference

[oliverblane]

This solved the ordering issue, thank you. But the chart still does not show 0 values for the days that have no data. Is there anything else to try? I can send a sample PBIX if required.

[Thejeswar]

Hi @oliverblane ,

Were you able to replace BLANKS() with 0s?

I doubt, if you don't have entry for all Dates and hours with BLANK as data, this replacing might not help.

May be if you can share a pbix file with some sample data that shows the issue, it might help for helping solve this

Regards,

[oliverblane]

Hi @Thejeswar, thanks for your help here. Unfortunately I am still unable to get this to work.

Here is a sample file demonstrating the issue I am having: Sample File.pbix

[Thejeswar]

Hi @oliverblane ,

You can check the below link. Looks like a similar ask. 

https://community.powerbi.com/t5/Desktop/Line-graph-draws-line-even-when-there-s-no-data/td-p/53405

Regards,

[oliverblane]

Unfortunately I could not get this to work. In the attached file you see the gaps still remain where there should be 0. I think as you mentioned the issue is due to my table not having rows for every date.

Do you know of any way round this?

[Icey · Accepted solution]

Hi @oliverblane ,

Please create another DateTime table with full date range and time range, and then create relationships between your fact table and this new DateTime table. Then use the Date and Hour column from this new DateTime table.

For creating DateTime table, please check: 

Creating a Date/Time Table that increases 1 hour on each row using DAX (vahiddm.com).

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[oliverblane]

This solution works perfectly, thank you @Icey!

[mh2587]

Measure = IF(SELECTEDVALUE('Table'[value])<>BLANK(),SELECTEDVALUE('Table'[value]),0)

[oliverblane]

Hi @mh2587, thanks for your reply. Unfortunately this did not work, do you have any other suggestions? Please see attached sample file: Sample File.pbix