How to show gap in the line chart when there is no-data for a time period

[srush]

I have some metrics data being sent every 5 minutes when a machine is running. There is no data when machine is not running.

Thus intermediate entries are not there in my dataset. (nor I should have them since dataset will become quite huge for lacs of machines).

When I plot a line chart for this metric, Power BI is joining the lines where there was no data(no intermediate entries).

Refer the below screenshot for it. There is no data for Oct 13 to 15. But the line is simply connected from last entry in 12 Oct to first entry in 16th Oct. Also data between 11 and 12th Oct is also connected. I don't want that to happen.

How can I configure that the lines are not connected when the difference between the 2 timestamps is greater than 5 or 10 minutes?

(PS: I tried using date table at "seconds" level granularity, but that does not work for my use case.)

[v-yifanw-msft]

Hi @srush ,

May I ask if your problem has been solved. If the above reply was helpful, you may consider marking it as solution. If the problem is not yet solved, please feel free to ask us a question.

Best Regards,

Ada Wang

[kpost]

1) make the X axis categorical

 

2) Select "Show items with no data" for the x axis value

 

3) Here is the result

 

I have implemented it as well in the attached .pbix file.  Note that this is not possible in your situation without a dimension table column being used along the x axis.

Hopefully this is helpful and you can modify the solution to fit your needs.  If not let me know and I'll do my best to help.

///Mediocre Power BI Advice, but it's free///

[srush]

Unfortunately, my data could be for a varied date range and may be 25-30 data points for each single day based on the machine status. Thus, when I change to categorical, the x-axis has a lot of scroll due to multiple values for a single day. Also, the date/time values could be at minutes granularity. Thus when I create a time dimension table, it would have date/time with valid data and next 4 minutes with no data. Thus I am ending up with scattered dots around the plot with no line connecting the two.

[kpost]

What, precisely, are you trying to understand when you look at this graph?

What information do you want it to convey?  

Average daily value, to show trends from week to week?
Is this an alert system to show when the values dropped above or below a certain threshold?
Are you trying to see when the machine is not working for extended periods of time, if so what do you define as "an extended period of time"?
Do you always need to be showing the entire data set?  Or would a date slicer be useful, or perhaps a dynamic X axis (always show only the last 2 weeks)?
Does every single data point need to be on the graph?

This is where I'd start if I were you.  Depending on the purpose of this graph, that will help to steer you in the right direction for funcionality and aesthetics.  Not a single one of the graphs I produce for my employer are just a matter of dragging data into visuals.  The formatting, measures used, x axis grouping, dynamic axis valus, all of that matters depending on what exactly you are trying to figure out or convey with each visual.

So I pose the question to you:  When the consumer of this report looks at your graph, what, precisely, are they trying to understand?  And as we solve this problem, we can keep your final goal in mind (disconnected lines for gaps in the data).

///Mediocre Power BI Advice, but it's free///