Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Relationship Causing Duplicates in Measure and Giving Incorrect Total

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

 

 

Dim Table:

Key

A

B

C

D

 

Tab1:

KeyRefData
AABCD10
AABCD10
BIJKL20

 

Tab2:

KeyData
A10
C20
D40

 

The desired output is to get 

KeyRefData Tab1Data Tab2

A

ABCD2010

B

IJKL20 

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?
"DAX Commands and Tips"
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    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.