Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Aradhana
Helper I
Helper I

Joining Fact with Master table to get expected result

Hi All,

 

I have two tables, Fact and Master.

Fact table

Date MachineShift PartReject reason No. of rejects
01-31-2022High speed 11EX59PLUSStation2_Missing_ORing_Total1434
01-31-2022High speed 11EX59PLUSStation9_Missing_Body_Total143
01-31-2022High speed 11EX59PLUSStation14_Missing_Ring_Total80
01-31-2022High speed 11EX59PLUSStation21_Missing_Post_Total158
01-31-2022High speed 11EX59PLUSStation27_Missing_Nut_Total309
01-31-2022High speed 11EX59PLUSStation35_Missing_Cont_Total108
01-31-2022High speed 11EX59PLUSStation35_W_HighNut_Total792
01-31-2022High speed 11EX59PLUSStation39_Missing_ORing_Total64
01-31-2022High speed 11EX59PLUSStation40_PostFail_Total238
01-31-2022High speed 11EX59PLUSStation41_Leak_Fail_Total0
01-31-2022High speed 11EX59PLUSStation42_Torque_Fail_Total111
01-31-2022High speed 21EX6PLUSStation2_Missing_ORing_Total1434
01-31-2022High speed 21EX6PLUSStation9_Missing_Body_Total143
01-31-2022High speed 21EX6PLUSStation14_Missing_Ring_Total80
01-31-2022High speed 21EX6PLUSStation21_Missing_Post_Total158
01-31-2022High speed 21EX6PLUSStation27_Missing_Nut_Total309
01-31-2022High speed 21EX6PLUSStation35_Missing_Cont_Total108
01-31-2022High speed 21EX6PLUSStation35_W_HighNut_Total792
01-31-2022High speed 21EX6PLUSStation39_Missing_ORing_Total64
01-31-2022High speed 21EX6PLUSStation40_PostFail_Total238
01-31-2022High speed 21EX6PLUSStation41_Leak_Fail_Total0
01-31-2022High speed 21EX6PLUSStation42_Torque_Fail_Total111

 

Master Table

MachinePartRejected component
High speed 1EX59PLUS861001-1047 O-RING
High speed 1EX59PLUS14436-01 BODY
High speed 1EX59PLUS11054-03 COMPRESSION RING
High speed 1EX59PLUS14443-01 POST
High speed 1EX59PLUS14065-01 NUT
High speed 1EX59PLUS12524-01 CONTINUITY MEMBER
High speed 2EX6PLUS861002-1047 O-RING
High speed 2EX6PLUS144-01 BODY
High speed 2EX6PLUS110-03 COMPRESSION RING
High speed 2EX6PLUS14443-01 POST
High speed 2EX6PLUS145-01 NUT
High speed 2EX6PLUS12533-01 CONTINUITY MEMBER

 

Result

Date MachineShift PartRejected componentNo. of rejects
01-31-2022High speed 11EX59PLUS861001-1047 O-RING2003
01-31-2022High speed 11EX59PLUS14436-01 BODY1860
01-31-2022High speed 11EX59PLUS11054-03 COMPRESSION RING1780
01-31-2022High speed 11EX59PLUS14443-01 POST1622
01-31-2022High speed 11EX59PLUS14065-01 NUT1313
01-31-2022High speed 11EX59PLUS12524-01 CONTINUITY MEMBER1205
01-31-2022High speed 21EX6PLUS861002-1047 O-RING2003
01-31-2022High speed 21EX6PLUS144-01 BODY1860
01-31-2022High speed 21EX6PLUS110-03 COMPRESSION RING1780
01-31-2022High speed 21EX6PLUS14443-01 POST1622
01-31-2022High speed 21EX6PLUS145-01 NUT1313
01-31-2022High speed 21EX6PLUS12533-01 CONTINUITY MEMBER1205

 

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

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

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
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors