Forum Discussion

JotaEfe's avatar
JotaEfe
New Member
4 years ago
Solved

Join 2 tables into a third using a factor

Hello,

 

I'm trying to get the following result:

 

Table A

DATEIDProduct
1/1/2021A123
2/1/2021A121
3/1/2021A128
1/1/2021B243
2/1/2021B233
3/1/2021B242
1/1/2021C565
2/1/2021C566
3/1/2021C556

 

 

Table B

DATEID2Product2
1/1/2021J5
2/1/2021J6
3/1/2021J5
1/1/2021Q3
2/1/2021Q2
3/1/2021Q3
1/1/2021M1
2/1/2021M1
3/1/2021M1.1

 

Table C

ID1ID2f
AJ0.5
AQ0.3
BJ0.5
BQ0.3
BM0.5
CM0.5

 

I want to Join Table A and B by using the relationship in table C.

For each Product of table A you have some asociated products of table B and the production is multiplied by a factor

 

So the output would be something like:

DATEIDProductAsoc. Prod
1/1/2021A1233.4
2/1/2021A1213.6
3/1/2021A1283.4
1/1/2021B2433.9
2/1/2021B2334.1
3/1/2021B2423.95
1/1/2021C5650.5
2/1/2021C5660.5
3/1/2021C5560.55

 

The first row would be: (5 * 0.5 + 3 * 0.3)

 

I hope is clear what im trying to attemp

 

Regards,

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi JotaEfe ,

     

    My Sample:

    I suggest you to try this code to create a calcualted column in Table A.

    Asoc. Prod =
VAR _Parameter =
    FILTER ( 'Table C', 'Table C'[ID1] = EARLIER ( 'Table A'[ID] ) )
VAR _PRODUCT2 =
    ADDCOLUMNS (
        _Parameter,
        "Product2",
            CALCULATE (
                SUM ( 'Table B'[Product2] ),
                FILTER ( 'Table B', 'Table B'[DATE] = EARLIER ( 'Table A'[DATE] ) )
            )
    )
RETURN
    SUMX ( _PRODUCT2, [f] * [Product2] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    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
    4 years ago

    Hi JotaEfe ,

     

    My Sample:

    I suggest you to try this code to create a calcualted column in Table A.

    Asoc. Prod =
VAR _Parameter =
    FILTER ( 'Table C', 'Table C'[ID1] = EARLIER ( 'Table A'[ID] ) )
VAR _PRODUCT2 =
    ADDCOLUMNS (
        _Parameter,
        "Product2",
            CALCULATE (
                SUM ( 'Table B'[Product2] ),
                FILTER ( 'Table B', 'Table B'[DATE] = EARLIER ( 'Table A'[DATE] ) )
            )
    )
RETURN
    SUMX ( _PRODUCT2, [f] * [Product2] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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