Force null display on a line chart

[Syndicate_Admin]

From a MySQL relational model, I have created a table in Power Query using 'Merge Queries' that relates each 'nombre_subestilo' (parameter of the MySQL 'substyles' table) with the 'anus' in which it appears (parameter of the MySQL 'lists' table; the 'substyles' table is related to the 'disks' table, this one with 'songs' and this one finally with 'lists'):

The table, which I have named 'combinada_subestilos_ano', looks like this:

nombre_subestilo year
Acoustic Rock 2005
Alternative Rock 2005
Alt-Country 2009
Alternative Dance 2009
Alternative Rock 2009
...

A substyle can appear several times in the same year.

Subsequently, using DAX, I have calculated the number of occurrences of each substyle per year:

subestilos_agrupados_por_ano =
GROUPBY
(
combinada_subestilos_ano, //Starting table (or expression returned by a table)
combinada_subestilos_ano[nombre_subestilo], //Groups by substyle
combinada_subestilos_ano[year], //Y per year
"occurrences", //Name of the resulting column
COUNTX(CURRENTGROUP(), combinada_subestilos_ano[nombre_subestilo]) //Recuento: CURRENTGROUP() equivale a la tabla temporal que se está creación al agrupar; Do you have the same thing to use [nombre_subestilo] or [00:00:00] and [00:00:00] and [00:00:00] and [00:
)

The 'subestilos_agrupados_por_ano' table looks like this:

nombre_subestilo year appearances
Acoustic Rock 1988 1
Acoustic Rock 1983 1
Alternative Dance 1983 1
Afrobeat 1980 1
Alternative Dance 1989 1
...

Well, the problem is that when rendering any substyle with a line chart, Power BI interpolates and ignores the years when the value should be 0; for example, for this particular case, the substyle appears in neither 1986 nor 1987, but Power BI displays them as if the value were 1:

I've tried the 'Show items without data' option, both in 'anus' and 'nombre_subestilo', but it doesn't work. The only option is to use a bar chart:

How might you force missing years to appear with a value of 0 on a line chart?

[v-cgao-msft · Accepted solution]

Hi @Syndicate_Admin ,

You may create a table for the x-axis and pass the values on the x-axis to the original metric with the SELECTEDVALUE() function.

Table1.

Table 2.

Table 2 = GENERATESERIES(MIN('Table 1'[Year]),MAX('Table 1'[Year]),1)

Measure = SUM('Table 1'[Value])

result.

Measure 2 = CALCULATE([Measure],'Table 1'[Year]=SELECTEDVALUE('Table 2'[Year]))+0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum