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.
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?
Solved! Go to Solution.
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.
@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!!
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.
@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,
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 🙂
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |