Line & Clustered Column Chart - Issue obtaining a Continous Line if X-Axis is Date

[JohnJoe123]

Hi Everyone,

Apologies if my Question Title was not clear enough.

Or if my Description is too Long.

So, I'm basically trying to fix a line & clustered column chart that I've made in Power BI Desktop.

I'm pulling all my data from a Database where I work.

I work in a Factory that makes Sensors.

And I'm making Power BI Visuals to see how many Alarms occur on a Machine when it is processing Sensors.

So for Right now, I've picked just one machine and I am currently looking at its data for September 2023.

And I'm looking at everything on a day-by-day basis. (Everything on 1st September, then 2nd September etc.)

The main metrics that I am looking at for each day are:

1. How Many Alarms Occur in a Day (These are the Columns in the Chart)

2. The Running Time of the Machine (in Total Minutes) that Day

- I Don't actually need to Show this on the Chart but it is important for Metric 3.

- Note also - that the Machine does not Run every Day - so this will sometimes be 0 Minutes

3. The Mean Time Between Failures (MTBF) (This is the Line on the Chart)

- My Boss said that the MTBF for each day is calculated from this Formula:

(Minutes the Machine Running that Day) Divided by (No. of Alarms that Day)

> So if the Machine is not Running that Date - it's Duration will be 0.

> And so obviously, no Alarms can occur if the Machine is not running - so it's Alarm Count for that Date will also be 0.

> So the MTBF will be 0 Divided by 0 - which by default displays 'null ' but I've changed it to '0' to make sense of it.

Here is a table of the Data that I currently have:

DATE	RUN TIME (Mins.)	ALARM COUNT	MTBF
01-Sep-23	1341	138	9.72
02-Sep-23	380	53	7.17
03-Sep-23	0	0	0.00
04-Sep-23	0	0	0.00
05-Sep-23	0	0	0.00
06-Sep-23	0	0	0.00
07-Sep-23	0	0	0.00
08-Sep-23	0	0	0.00
09-Sep-23	430	134	3.21
10-Sep-23	0	0	0.00
11-Sep-23	0	0	0.00
12-Sep-23	0	0	0.00
13-Sep-23	388	57	6.81
14-Sep-23	0	0	0.00
15-Sep-23	0	0	0.00
16-Sep-23	0	0	0.00
17-Sep-23	0	0	0.00
18-Sep-23	0	0	0.00
19-Sep-23	0	0	0.00
20-Sep-23	57	10	5.70
21-Sep-23	1315	174	7.56
22-Sep-23	1248	132	9.45
23-Sep-23	843	160	5.27
24-Sep-23	0	0	0.00
25-Sep-23	0	0	0.00
26-Sep-23	0	0	0.00
27-Sep-23	0	0	0.00
28-Sep-23	0	0	0.00
29-Sep-23	0	0	0.00
30-Sep-23	0	0	0.00

My Desired Graph would be this:

Notice how in this desired graph, if the Machine does not run that day (and hence the MTBF is null and void for that date), the MTBF Line sumply continues to the next available figure. (In the first instance in the desired image; it jumps straight from 02SEP23 to 09SEP23 since the machine was not running between these 2 Dates).

However, I cannot seem to obtain this desired result.

The Default Graph I get from the Data my table is this: (MTBF is 0 for Days Machine is not Running)

If I replace the MTBF '0' values with 'null'; I get this:

And if I replace the Dates where the Machine is not running with 'null', I get this:

[HotChilli]

You will have to remove those 0 values (either from the data or from the visual) to get the chart you want.

You can experiment with a slicer that holds all the MTBF values.  Select everything that is not zero and I reckon you will get the right chart.

So, if you prove that, you can either remove all the zero rows in power query or use a slicer, somethinglike that.