Forum Discussion

mukherjeerahul's avatar
mukherjeerahul
New Member
2 years ago
Solved

How toCreate a calculated column by multiplying two columns where one column is in a different table

How to Create a calculated column by multiplying two columns where one column is in a different table?

 

Tables are connected with M2M cardinality so RELATED function dax is not working

 

Kindly help

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi mukherjeerahul ,

     

    Thanks for the reply from Wilson_ .

     

    Here is the sample data I created:

     

    TableA:

    ProductID

    Value

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

     

    Table B:

    ProductID

    Value

    1

    1

    2

    3

    3

    5

    4

    7

    1

    9

    2

    2

    3

    4

    4

    6

    1

    8

    2

    10

    3

    1

    4

    3

    1

    5

    2

    7

    3

    9

    4

    2

    1

    4

    2

    6

    3

    8

    4

    10

     

    This is the model diagram of my two tables:

     

    Please try:

     

    Create an intermediate table to aggregate values from TableA and TableB by ProductID, the syntax is as follows:

    Table =
SUMMARIZE(
     TableA,
     TableA[ProductID],
     "SumValueA", SUMX(FILTER(TableA, TableA[ProductID] = EARLIER(TableA[ProductID])), TableA[Value]),
     "SumValueB", SUMX(FILTER(TableB, TableB[ProductID] = EARLIER(TableA[ProductID])), TableB[Value])
)

     

    Create a new column to calculate the product of 'Table'[SumValueA] and 'Table'[SumValueB]

    Column = 'Table'[SumValueA] * 'Table'[SumValueB]

     

    The page effect is as follows:

     

    If you have any further questions please feel free to contact me.

     

    pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    Demo.pbix34 KB

2 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    Hi mukherjeerahul,

     

    The equivalent function of RELATED when you're trying to pull values from the many side of the relationship is RELATED table. However, you will need some kind of aggregate function to convert the returned table into a scalar value. Hope that helps. Hard to provide much more guidance than that without any additional detail.


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

     

    P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mukherjeerahul ,

     

    Thanks for the reply from Wilson_ .

     

    Here is the sample data I created:

     

    TableA:

    ProductID

    Value

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

    1

    1

    2

    1

    3

    1

    4

    1

     

    Table B:

    ProductID

    Value

    1

    1

    2

    3

    3

    5

    4

    7

    1

    9

    2

    2

    3

    4

    4

    6

    1

    8

    2

    10

    3

    1

    4

    3

    1

    5

    2

    7

    3

    9

    4

    2

    1

    4

    2

    6

    3

    8

    4

    10

     

    This is the model diagram of my two tables:

     

    Please try:

     

    Create an intermediate table to aggregate values from TableA and TableB by ProductID, the syntax is as follows:

    Table =
SUMMARIZE(
     TableA,
     TableA[ProductID],
     "SumValueA", SUMX(FILTER(TableA, TableA[ProductID] = EARLIER(TableA[ProductID])), TableA[Value]),
     "SumValueB", SUMX(FILTER(TableB, TableB[ProductID] = EARLIER(TableA[ProductID])), TableB[Value])
)

     

    Create a new column to calculate the product of 'Table'[SumValueA] and 'Table'[SumValueB]

    Column = 'Table'[SumValueA] * 'Table'[SumValueB]

     

    The page effect is as follows:

     

    If you have any further questions please feel free to contact me.

     

    pbix file is attached.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    Demo.pbix34 KB