topic Passing values in tables with relation many to many in DAX Commands and Tips

Passing values in tables with relation many to many

STRUSINIO — Mon, 22 May 2023 14:02:37 GMT

Hey,
im facing the problem with passing values between two tables in many to many relationship (product to product).
Table1:

Table 2:

What im trying to do is to create a calculated column in Table 1 that will show Sum of sales for specific product but only once.
Expected result:

already played with sumx function in different ways, but it always replicate my results for each of three rows in that example.
Im woundering if that expected output is even possible to create.

Thanks in advance for any guidence

Re: Passing values in tables with relation many to many

Anonymous — Wed, 24 May 2023 02:43:45 GMT

Hi STRUSINIO ,

Your expected result is blank, so i just could give solution according to your description.

Please try to create a calculate column with below dax formula:

Column = VAR _a = [Product] VAR tmp = FILTER ( ALL ( Table2 ), [Product] = _a ) RETURN SUMX ( tmp, [Sales] )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.