Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a Table with Incoming Customer Defects.In some Weeks there are no customer defects reported. So i want to add Missing WEEK_NUM of that year and against those Missing Week_Num, Count as Zero.
Here in the table you will see that Week Num 54, 1, 5, 8... missing.
Can you please help me how to insert those in Power BI.
PROJECT_KEY | YEAR | WEEK_NUM | Count |
ABC | 2022 | 45 | 1 |
ABC | 2022 | 46 | 3 |
ABC | 2022 | 47 | 5 |
ABC | 2022 | 48 | 11 |
ABC | 2022 | 49 | 14 |
ABC | 2022 | 50 | 4 |
ABC | 2022 | 51 | 8 |
ABC | 2022 | 52 | 5 |
ABC | 2022 | 53 | 1 |
ABC | 2023 | 2 | 2 |
ABC | 2023 | 3 | 4 |
ABC | 2023 | 4 | 5 |
ABC | 2023 | 6 | 2 |
ABC | 2023 | 7 | 4 |
ABC | 2023 | 8 | 2 |
ABC | 2023 | 9 | 1 |
ABC | 2023 | 10 | 4 |
ABC | 2023 | 11 | 1 |
ABC | 2023 | 12 | 5 |
ABC | 2023 | 13 | 1 |
ABC | 2023 | 14 | 4 |
ABC | 2023 | 15 | 2 |
ABC | 2023 | 16 | 7 |
ABC | 2023 | 17 | 7 |
ABC | 2023 | 19 | 6 |
ABC | 2023 | 20 | 3 |
ABC | 2023 | 21 | 4 |
ABC | 2023 | 22 | 3 |
ABC | 2023 | 23 | 1 |
ABC | 2023 | 24 | 1 |
ABC | 2023 | 25 | 4 |
ABC | 2023 | 26 | 1 |
ABC | 2023 | 29 | 2 |
ABC | 2023 | 32 | 1 |
ABC | 2023 | 33 | 3 |
ABC | 2023 | 34 | 1 |
ABC | 2023 | 37 | 1 |
ABC | 2023 | 39 | 6 |
ABC | 2023 | 40 | 6 |
ABC | 2023 | 42 | 1 |
ABC | 2023 | 44 | 1 |
ABC | 2023 | 46 | 1 |
ABC | 2023 | 47 | 3 |
ABC | 2023 | 48 | 1 |
ABC | 2023 | 49 | 2 |
ABC | 2023 | 50 | 3 |
ABC | 2023 | 51 | 2 |
ABC | 2023 | 52 | 1 |
Solved! Go to Solution.
Hello @JeevanMallya
Just create a relationship based on yourtable[weeknum] and the Weeknum table created using generate series and use weeknum from the custom table created (Weeknum[value] from below SS).
If this post helps, then please consider Accepting it as the solution to help the others find it more quickly. Appreciate you kudos!!
Hello @JeevanMallya
The attached file has the solution that you need. Let me know if this works for you.
If this post helps, then please consider Accepting it as the solution to help the others find it more quickly. Appreciate you kudos!!
Hi @NaveenGandhi : Thank you help.
Can you please help me in understanding how to build this.I see from the attached file that a new table Week_Num is created that generates series from 1 to 54 week Numbers and a Measure Count is created for inserting Zeros.
I do see that Value and Count- gives the requirement. But how do i merge this in single table (Data table in this case) since i want this in single table that will help me later visually build and display Data for all weeks. LIke
PROJECT_KEY | YEAR | WEEK_NUM | Count |
ABC | 2022 | 45 | 1 |
ABC | 2022 | 46 | 3 |
ABC | 2022 | 47 | 5 |
ABC | 2022 | 48 | 11 |
ABC | 2022 | 49 | 14 |
ABC | 2022 | 50 | 4 |
ABC | 2022 | 51 | 8 |
ABC | 2022 | 52 | 5 |
ABC | 2022 | 53 | 1 |
ABC | 2022 | 54 | 0 |
ABC | 2023 | 1 | 0 |
ABC | 2023 | 2 | 2 |
ABC | 2023 | 3 | 4 |
ABC | 2023 | 4 | 5 |
ABC | 2023 | 5 | 0 |
ABC | 2023 | 6 | 2 |
ABC | 2023 | 7 | 4 |
ABC | 2023 | 8 | 2 |
ABC | 2023 | 9 | 1 |
ABC | 2023 | 10 | 4 |
ABC | 2023 | 11 | 1 |
ABC | 2023 | 12 | 5 |
ABC | 2023 | 13 | 1 |
ABC | 2023 | 14 | 4 |
ABC | 2023 | 15 | 2 |
ABC | 2023 | 16 | 7 |
ABC | 2023 | 17 | 7 |
ABC | 2023 | 19 | 6 |
ABC | 2023 | 20 | 3 |
ABC | 2023 | 21 | 4 |
ABC | 2023 | 22 | 3 |
ABC | 2023 | 23 | 1 |
ABC | 2023 | 24 | 1 |
ABC | 2023 | 25 | 4 |
ABC | 2023 | 26 | 1 |
ABC | 2023 | 29 | 2 |
Can you please help.
Hello @JeevanMallya
Just create a relationship based on yourtable[weeknum] and the Weeknum table created using generate series and use weeknum from the custom table created (Weeknum[value] from below SS).
If this post helps, then please consider Accepting it as the solution to help the others find it more quickly. Appreciate you kudos!!
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |