Forum Discussion
Show dates with no data on continuous date axis
I have data in a table recording utilization of resources in a format like this:
Basically, we record the name of the resource, when the work has started, and for how long it was being worked.
And what I want to achieve is a chart with aggregated time the resource work each day, like this:
When no working activity has been recorded that day, it should be shown as 0.
But when I add the data to Power BI, it will show dates with no data as a line between other data points, not 0. Like this:
I have read elsewhere that I need to create a separate DateTable to summarize all the data, is that correct? But then, how can I dynamically add columns based on resource name existed in the source table? I don't want to modify the chart each time we add a C or D or E to this list, which could happen very frequently. Is there a way to achieve this?
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.
9 Replies
- Hi Phil_Seamark No. I just tried area chart but it does not work either. The issue I'm having is, I don't know how can I construct a separate date table without knowing all resource names (Device) beforehand. Perhaps you could give me an example with the sample data shown above please?
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.
