The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I have two tables, Fact and Master.
Fact table
Date | Machine | Shift | Part | Reject reason | No. of rejects |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station2_Missing_ORing_Total | 1434 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station9_Missing_Body_Total | 143 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station14_Missing_Ring_Total | 80 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station21_Missing_Post_Total | 158 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station27_Missing_Nut_Total | 309 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station35_Missing_Cont_Total | 108 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station35_W_HighNut_Total | 792 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station39_Missing_ORing_Total | 64 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station40_PostFail_Total | 238 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station41_Leak_Fail_Total | 0 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | Station42_Torque_Fail_Total | 111 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station2_Missing_ORing_Total | 1434 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station9_Missing_Body_Total | 143 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station14_Missing_Ring_Total | 80 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station21_Missing_Post_Total | 158 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station27_Missing_Nut_Total | 309 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station35_Missing_Cont_Total | 108 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station35_W_HighNut_Total | 792 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station39_Missing_ORing_Total | 64 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station40_PostFail_Total | 238 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station41_Leak_Fail_Total | 0 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | Station42_Torque_Fail_Total | 111 |
Master Table
Machine | Part | Rejected component |
High speed 1 | EX59PLUS | 861001-1047 O-RING |
High speed 1 | EX59PLUS | 14436-01 BODY |
High speed 1 | EX59PLUS | 11054-03 COMPRESSION RING |
High speed 1 | EX59PLUS | 14443-01 POST |
High speed 1 | EX59PLUS | 14065-01 NUT |
High speed 1 | EX59PLUS | 12524-01 CONTINUITY MEMBER |
High speed 2 | EX6PLUS | 861002-1047 O-RING |
High speed 2 | EX6PLUS | 144-01 BODY |
High speed 2 | EX6PLUS | 110-03 COMPRESSION RING |
High speed 2 | EX6PLUS | 14443-01 POST |
High speed 2 | EX6PLUS | 145-01 NUT |
High speed 2 | EX6PLUS | 12533-01 CONTINUITY MEMBER |
Result
Date | Machine | Shift | Part | Rejected component | No. of rejects |
01-31-2022 | High speed 1 | 1 | EX59PLUS | 861001-1047 O-RING | 2003 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | 14436-01 BODY | 1860 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | 11054-03 COMPRESSION RING | 1780 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | 14443-01 POST | 1622 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | 14065-01 NUT | 1313 |
01-31-2022 | High speed 1 | 1 | EX59PLUS | 12524-01 CONTINUITY MEMBER | 1205 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | 861002-1047 O-RING | 2003 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | 144-01 BODY | 1860 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | 110-03 COMPRESSION RING | 1780 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | 14443-01 POST | 1622 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | 145-01 NUT | 1313 |
01-31-2022 | High speed 2 | 1 | EX6PLUS | 12533-01 CONTINUITY MEMBER | 1205 |
Calculation:
No of rejects in Result table will be,
2003= sum of no. of rejects from fact table 2 to 11
1860=sum of no. of rejects from fact table 3 to 11
1780 = sum of no. of rejects from fact table 4 to 11
.
.
1205=sum of no. of rejects from fact table 7 to 11
Thanks in Advance.
Thanks,
Aradhana
Hi @Aradhana ,
As Mark said, there seems to be some problems with the data model. What is the join field between the main table and the fact table, and does it have a unique identifier?
You can refer to the following document to modify the data model, so that I can do further testing.
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
Looking forward to your reply.
Best Regards,
Henry
@Aradhana , what is the join key here? If there are more than one columns then you can create a concatenated column in power query and merge two table post that
Append and merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-query