Show filtered data as 0 in line chart

[eternalphoenix]

Everything I've found seems to be trying to solve a different problem. I am actually currently using app.powerbi instead of desktop, but I'm not opposed to moving my reports to desktop if I have to, it's just easier for me to automate reports from the service with my allocated permissions.

I have data in a OneDrive Excel constantly added to by PowerAutomate that is generally about like this:

Date	Time (24hr)	EQ Name	alm type	SubEQ	Batch#	ChemID	Phase	Hour	Min	Dec. Hour (nearest 0.25)
mm/dd/yyyy	HH:MM:ss	EQ1	temp	pump	#######	XX##X###	recirc	HH	MM	HH.(MM/60)
mm/dd/yyyy	HH:MM:ss	EQ1	psi	pump	#######	XX##X###	dispense	HH	MM	HH.(MM/60)
mm/dd/yyyy	HH:MM:ss	EQ2	psi	infeed	######	XX##X###	recirc	HH	MM	HH.(MM/60)
mm/dd/yyyy	HH:MM:ss	EQ3	jam	auger	#######	XX##X###	start	HH	MM	HH.(MM/60)

While the future of this file isn't certain (it's only a month old, which is also about how long I've been using PBI), it could potentially have data for an entire year or longer. I have multiple reports (exported to PDF by PowerAutomate and emailed) with the following graphics:
Stacked bar, # of Alarms by EQ and Type

Stacked bar, # of Alarms by Phase and Type

Table, Top 5 batch#

Table, Top 5 ChemID

Table, Top 5 Type

Line Chart, # of Alarms by Hour

Currently, the reports are daily, weekly, and monthly, using a relative date filter. On the daily and weekly, we often have long time periods (2 or more hours) with no new data, but the line chart automatically connects those data points. I've tried multiple suggestions I've found in older forum posts, including some that require PBI Desktop for DAX inquiries, and nothing seems to work. I want to figure out a way to dynamically and automatically plot those time slots as 0 if there is no data. Image below shows a snippet of 14:30 to 16:30 and I HOPE it will show what I currently have (orange) and what I want it to be (red).

[Anonymous · Accepted solution]

Hi @eternalphoenix 

Agree with NordicDrow.

It is recommended to create datetable and timetable, which help to display consecutive date segments and time periods on the X-axis. Create unidirectional one-to-many relationships between the new tables and the data table. 

DateTable = CALENDAR(MIN('Table'[Date]), MAX('Table'[Date]))

TimeTable = 
SELECTCOLUMNS(
    GENERATESERIES(0, 23.75, 0.25),
    "Time", [Value]
)

Then create a measure to assign counts of time periods with no data to 0.

CountDecHour = COUNTX('Table', 'Table'[Dec.Hour]) + 0

Here is a similar solved case for your reference:

Solved: Plot zero when there is no data in line chart solu... - Microsoft Fabric Community

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

[NordicDrow]

Hi there!

First of all, I will be clear, I have always worked with PBI Desktop so not sure about all the differences. Now to the answer.

It depend on what your x axis is based on, but basically you need to create a separate table with all values for x axis listed. Then relate them.

So, if you x axis is a date column, you would create a date table, then relate date clmn from each table and use the date column from the date table in the x axis (relationship should not be bidirectional to avoid the main table filtering the date table).

There are many tutorials on how to create a quick date table, as its a very common thing to do.

Hope this helps, tell me if you need anything else.

[eternalphoenix]

I should clarify (my fault, I realize on reading back I didn't make it clear). The line chart currently uses x-axis as the Dec. Hour (decimal hour), and the y-axis is Count of Dec. Hour (since every entry is an alarm that I want to count). I did have to adjust the x-axis to be Continuous, instead of Categorical, and manually set the max to 23.75 (just for visual formatting, since the alternative was an auto-max that went to 25). We wanted the decimal hour to show 15-minute intervals because the original line graph charted in whole hours, so the 15-min interval takes away the ambiguity of "was this at 4:02am or was it more like 4:57am meaning it really belongs in the 5am data point?"

It may also help to make it clear that in the weekly and monthly reports, the time is still JUST a 24-hour span with the total count for the entire report duration (report duration is created by filtering on relative date). We don't care so much about the timeline of alarms as we care about answering questions like "do we have most of our alarms at shift change? What about lunch time? One shift versus the other?" as well as being able to see the impact of changes to policy/process on those same questions.

All columns are generated by PowerAutomate based on emails received in a shared inbox with the exception of the decimal hour that is generated by formula (hour + min/60, MROUND to neareast 0.25)