Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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:
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |