Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Community,
Hope you are well.
I need your help with the below challenge.
I want to produce a table at which :
For example, the correct answer to the question "what is the value of hard benefit 1 that the project PR1 has delivered in 2020?" will be 104+27, instead of 27.
Table 1 : Raw Data
Project | Year | Hard Benefit 1 | Hard Benefit 2 | Hard Benefit 3 |
PR1 | 2019 | 104 | 0 | 108 |
PR1 | 2020 | 27 | 41 | 109 |
PR1 | 2021 | 105 | 67 | 0 |
PR2 | 2021 | 88 | 63 | 30 |
PR2 | 2022 | 110 | 0 | 79 |
PR3 | 2020 | 98 | 52 | 66 |
Table 2 : Desired Outcome
2019 | 2020 | 2021 | 2022 | 2023 | ||
PR1 | Hard Benefit 1 | 104 | 104+27 | 104+27+105 | 104+27+105 | 104+27+105 |
Hard Benefit 2 | 0 | 41 | 41+67 | 41+67 | 41+67 | |
Hard Benefit 3 | 108 | 109 | 0 | 0 | 0 | |
PR2 | Hard Benefit 1 | 0 | 0 | 88 | 88+110 | 88+110 |
Hard Benefit 2 | 0 | 0 | 63 | 63 | 63 | |
Hard Benefit 3 | 0 | 0 | 30 | 79 | 79 | |
PR3 | Hard Benefit 1 | 0 | 98 | 98 | 98 | 98 |
Hard Benefit 2 | 0 | 52 | 52 | 52 | 52 | |
Hard Benefit 3 | 0 | 66 | 0 | 0 | 0 |
Hope my explanation makes sense.
Thank you in advance,
George
Solved! Go to Solution.
@Anonymous
sry, I fixed this. Please see the attachment.
Proud to be a Super User!
Hello @ryan_mayu
Thank you for your reply. I noticed that your solution has an error. More specifically, it summarizes the values per year, but it ignors the breakdown per benefit type.
For example, the value of project PR1, hard benefit 1, year 2019 is 104. Your solution shows 212 instead, which is the sum of hard benefit 1 (value 104) and hard benefit 3 (value 108) for year 2019.
Thank you,
George
Hello @ryan_mayu ,
I really appreciate your quick response.
Your approach is perfect.
Please allow me one more question before I accept it as the solution. May I only ask if there is a way to produce the same solution, without the additional tables/relationships that you created?
Thank you,
George
@Anonymous
yes, that was my first attempt.However I found what I got is not as same as what you need. So I tried another workaround to get your expected output.
Proud to be a Super User!
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |