Joint 3 tables with 2 relationship (second)

[Anonymous]

Hi all, 

I have a situation about joining multiple tables data.

1. Each ".Ref No.2" has multiple products,

2. One "Ref No.1", may contain different "Ref No.2", with same products 

Is Power BI able to do that?

Thanks a lot

1. Matching indicator

Ref No.1	Ref No.2
C0001	A0001
C0001	A0002
C0002	A0003
C0002	A0004

2. Table small

SKU number	Ref No.2	Qty
1022-00200-5118-34-10	A0001	1
1022-00200-5118-36-10	A0001	2
1022-00200-5118-36-10	A0002	2
1022-00200-5118-34-10	A0003	3
1022-00200-5118-35-10	A0004	2

3. Table big

SKU	Qty	Ref No.1
1022-00200-5118-34-10	1	C0001
1022-00200-5118-36-10	4	C0001
1022-00200-5118-34-10	4	C0002
1022-00200-5118-37-10	2	C0002

Ideal Result:

Ref No.1	Ref No.2	SKU number	Small	Big
C0001	A0001	1022-00200-5118-34-10	1	1
C0001	A0001	1022-00200-5118-36-10	2	2
C0001	A0002	1022-00200-5118-36-10	2	2
C0002	A0003	1022-00200-5118-34-10	3	4
C0002	A0004	1022-00200-5118-35-10	2
C0002		1022-00200-5118-37-10		2

[az38]

Hi @Anonymous 

SKU 1022-00200-5118-36-10 has Qty=4 in the 'Table big' why the row

C0001

A0001

1022-00200-5118-36-10

2

2

has a 2 in BIG column?

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

hi @az38  because "C0001" contains "A0001" & A0002", each of them has 2 "1022-00200-5118-36-10", so total is 4

[v-diye-msft]

Hi  @Anonymous 

Sorry It's not clear, in the table Big:

1022-00200-5118-36-10

4

C0001

you said the Qty is 4 coz including the A0001 & A0002, but there no Ref No.2 column in table big, how can it be split into 2 & 2 in the results table? 

[az38]

@Anonymous  its clearly unclear. how shoul calculate column Big in output table?

whats difference between Qty in big and small tables? could you show an math formula how do you plan get values in Big nd small columns?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 

So 1 container can contain multiple invoices, refer to the "indicator table"

However, the SKUs / Quantity in container may different from those in invoices, due to some human error

So I want to use power BI to identify all the errors

I asked this question before but I found that I was too simplified the question:

https://community.powerbi.com/t5/Desktop/Joint-3-tables-with-2-relationship/m-p/859328#M412274

Thanks!