Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

Force null display on a line chart

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:

wg4q4.png

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:

nebwL.png

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

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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.

vcgaomsft_0-1679899142970.png

Table 2.

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

vcgaomsft_1-1679899182573.png

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

vcgaomsft_2-1679899236076.png

result.

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

vcgaomsft_3-1679899294997.png

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

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

The problem is that my table has an additional column with the substyles; Actually, every year is represented because there is always at least some substyle for each year. What I need is for substyle/year pairs with value 0 to appear when that substyle does not exist for that year. For example, for "Acoustic rock" these are the equivalences:

Captura.JPG

To represent it as I want it in a line graph, I would need for all the remaining years between 1960 and 2009, "occurrences" to be listed with a 0.

v-cgao-msft
Community Support
Community Support

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.

vcgaomsft_0-1679899142970.png

Table 2.

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

vcgaomsft_1-1679899182573.png

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

vcgaomsft_2-1679899236076.png

result.

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

vcgaomsft_3-1679899294997.png

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.