Forum Discussion
Show dates with no data on continuous date axis
Hi leozuo-flex
Here is a link to a PBIX file that does what you are after.
https://1drv.ms/u/s!AtDlC2rep7a-oxJsoAdq92qNMpqo
Basically the problem is your orginal table simply lacks the necessary rows for the measures to work off. So for device A, there is no row for the 2nd of Jan so it can't generate a value. You cannot add rows to existing phyicical tables in DAX, but you can create new tables and plug the gaps in there.
The PBIX File I generated starts with your data in a table called 'Table'. I then generate a new table with some DAX magic that fills gaps with zeros for days with no data.
Hopefully you can follow and adapt to your own data.
Hi leozuo-flex
Here is a link to a PBIX file that does what you are after.
https://1drv.ms/u/s!AtDlC2rep7a-oxJsoAdq92qNMpqo
Basically the problem is your orginal table simply lacks the necessary rows for the measures to work off. So for device A, there is no row for the 2nd of Jan so it can't generate a value. You cannot add rows to existing phyicical tables in DAX, but you can create new tables and plug the gaps in there.
The PBIX File I generated starts with your data in a table called 'Table'. I then generate a new table with some DAX magic that fills gaps with zeros for days with no data.
Hopefully you can follow and adapt to your own data.
Hi Phil,
Thanks for this example. I have modified it to work with my data, it works like a charm. I think I understand DAX better now on how to do these things :)
Phil_Seamark Please I need your help to solve a problem similar to this one. I have gone around the internet for 1 week without finding a solution, I think I'm losing my mind.
Phil_Seamark Please I need your help to solve a problem similar to this one. I have gone around the internet for 1 week without finding a solution, I think I'm losing my mind.
hi Phil_Seamark
Please I need your help to solve a problem similar to this one. I have gone around the internet for 1 week without finding a solution, I think I'm losing my mind.
Phil_Seamark This answer is very close to what I need however I am needing more than two columns returned in the GapFiller Variable. Example to what I am trying to do below. Is this possible? Thanks so much!!
VAR GapFiller = SELECTCOLUMNS(GENERATE(VALUES('Table'[Device]),CALENDAR(MIN('Table'[Work Start]),MAX('Table'[Work Start]))) ,"Device" , [Device] ,--"Word Start", [Date] ,--"Time Running",0 ,"Date (Calculated)",[Date],"ColumnA", [ColumnA],"ColumnB", [ColumnB],"ColumnC", [ColumnC])
