Joining Fact with Master table to get expected result

[Aradhana]

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

[v-henryk-mstf]

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

[amitchandak]

@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