I have two tables - one with KPI data and another one with invoice data. The tables are linked on Material and Month. The KPI table has data for each month while the invoice data is missing for some months. What I want is a line chart with KPI and KPI_Month as dimensions and Max(KPI_Value) as measure. After selecting a material, the chart should display the associated KPI for all months and not only for the months with invoice data. Please see the gif below and notice how some months disappear after a material gets selected:
Here is the sample data:
KPI: LOAD KPI, KPI_Value, DATE(DATE#(KPI_Month, 'MM.YYYY')) AS KPI_Month, // Material Material & '-' & DATE(DATE#(KPI_Month, 'MM.YYYY')) AS Material_Month_Key INLINE [ KPI,KPI_Value,KPI_Month,Material Target_LBSD,101,07.2016,5000 Target_LBSD,102,08.2016,5000 Target_LBSD,103,09.2016,5000 Target_LBSD,104,10.2016,5000 Target_LBSD,102,11.2016,5000 Target_LBSD,104,12.2016,5000 Target_LBSD,103,01.2017,5000 Target_LBSD,102,02.2017,5000 Target_LBSD,101,03.2017,5000 Target_LBSD,102,04.2017,5000 Target_LBSD,103,05.2017,5000 Target_LBSD,104,06.2017,5000 Target_EGDA,106,07.2016,6000 Target_EGDA,107,08.2016,6000 Target_EGDA,108,09.2016,6000 Target_EGDA,106,10.2016,6000 Target_EGDA,108,11.2016,6000 Target_EGDA,108,12.2016,6000 Target_EGDA,109,01.2017,6000 Target_EGDA,107,02.2017,6000 Target_EGDA,108,03.2017,6000 Target_EGDA,109,04.2017,6000 Target_EGDA,120,05.2017,6000 Target_EGDA,106,06.2017,6000 ]; Data: LOAD Material AS Material_ID, Material_Name, DATE(DATE#(Invoice_Month, 'MM.YYYY')) AS Invoice_Month, Invoice_Value, Invoice_Quantity, Material & '-' & DATE(DATE#(Invoice_Month, 'MM.YYYY')) AS Material_Month_Key INLINE [ Material,Material_Name,Invoice_Month,Invoice_Value,Invoice_Quantity 5000,LBSD,07.2016,150,2500 5000,LBSD,09.2016,140,2500 5000,LBSD,11.2016,130,2500 5000,LBSD,01.2017,145,2500 5000,LBSD,03.2017,155,2500 5000,LBSD,05.2017,150,2500 6000,EGDA,07.2016,160,2900 6000,EGDA,08.2016,180,2900 6000,EGDA,12.2016,150,2900 6000,EGDA,01.2017,155,2900 6000,EGDA,04.2017,165,2900 6000,EGDA,05.2017,170,2900 ];
This should not be too complicated but I was not able to figure it out. I hope somebody has an idea.