Show continuous line in graph of count over year/quarter (no more dots!)

[KCfromDC]

I have a line graph that has some categories with no values for certain quarters, so I'm seeing periodic "dots" and disconnected lines. Data is a huge table, but in essence, I'm counting the number of antibiotics given per Age Group by Quarter and Year, marked as such:

Date	Age Group	AB
03/01/2024	20-29	0
18/01/2024	30-39	1
04/02/2024	40-49	0
15/02/2024	40-49	1
01/03/2024	50-59	1
05/03/2024	50-59	1
17/03/2024	60-69	1
20/03/2024	70-79	0
28/03/2024	80+	0

Here's what an aggregated version would look like:

Year/Quarter	Quarter	Age group	AB count
2024	1	20-29	0
2024	1	30-39	1
2024	1	40-49	1
2024	1	50-59	2
2024	1	60-69	1
2024	1	70-79	0
2024	1	80+	0

Y axis is the count of AB, X axis is the year and then the quarter underneath. Years range from 2014 -2024:

How do I get it so any quarters with a 0 count go back to the X axis as, well, 0? See below for example:

I've tried two ways: one, using the regular Date in the main table and drilling down, or making a separate date table. Even if I get rid of Quarter and make Year categorical, it still presents disconnected lines:

[Uzi2019 · Accepted solution]

Hi @KCfromDC 

I have taken your sample data. so i am getting this result before calculation.

follow these steps:

1: create calendar table  and add quater column to it

2: then create a measure with following logic:

Count AB= 

Count AB = Sum('Blood Grp'[AB])+0

Step3: add your chart take values from calendar table on X-axis and count Ab measure value on Y axis. and age group on legends

I hope i answered your question!

[KCfromDC]

Brilliant, thank you!

[MFelix]

Hi @KCfromDC ,

Not sure how you have your data in the visual but you should add a 0 to your calculation so when it's blank it will return 0.

This can be achieved using a +0 in the end of the measure or a COALESCE syntax.