Relationship Causing Duplicates in Measure and Giving Incorrect Total

[sid-poly]

There are 3 Tables, Dim, Tab1, and Tab 2 with the relationship defined below,

Dim Table:

Key

A

B

C

D

Tab1:

Key	Ref	Data
A	ABCD	10
A	ABCD	10
B	IJKL	20

Tab2:

Key	Data
A	10
C	20
D	40

The desired output is to get 

Key	Ref	Data Tab1	Data Tab2
A	ABCD	20	10
B	IJKL	20
C			20
D			40

Tried using the measure 

CALCULATE(SUMX(Tab2,Tab2[Data]), ALLSELECTED(Dim[Key]))

end up with duplicate values and Cross join of the table with the DIM giving

A

A

A

B

B

B

C
C

C

D
D
D 

What is the reason behind the duplication and how to correct it?

[Anonymous · Accepted solution]

Hi @sid-poly 

You can use the append feature in PowerQuery to implement your needs.

When you add the "ref" column to the table, the result can only be like this:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.