topic Create a measure using relations and performing a multiplication in DAX Commands and Tips

Create a measure using relations and performing a multiplication

Anonymous — Tue, 27 Sep 2022 12:24:24 GMT

Having three tables, i need to come up with a measure that does a multiplication using values from those three tables, however making use of the existing relationships.

Article

Id	Name	Value
1	aaa	1
2	bbb	2

ArticleDetail1

Id	ArticleId	Value2
6	1	0.5
7	2	0.6

ArticleDetail2

Id	ArticleId	Value3
4	1	2
5	2	3

Article, ArticleDetail1 and ArticleDetail2 are linked using the Id->ArticleId columns.

I need to get Value * Value2 * Value3

So for aaa i get 1 * 0.5 * 2 = 1, for bbb i get 2 * 0.6 * 3 = 3.6

And in total, using this on a card, i would get 4.6. What does the DAX look like for this?

Re: Create a measure using relations and performing a multiplication

johnt75 — Tue, 27 Sep 2022 12:46:53 GMT

Are the relationships from Article one-to-many or one-to-one? If they are one-to-many, which value from the many side should the calculation use?

Re: Create a measure using relations and performing a multiplication

Jihwan_Kim — Tue, 27 Sep 2022 12:55:36 GMT

Hi,

I am not sure how your datamodel looks like, but I assume the relationship looks like below.

Please check the below picture and the attached pbix file.

Expected result measure: = SUMX ( Article, Article[Value] * SUMX ( RELATEDTABLE ( ArticleDetail1 ), ArticleDetail1[Value2] ) * SUMX ( RELATEDTABLE ( ArticleDetail2 ), ArticleDetail2[Value3] ) )

Re: Create a measure using relations and performing a multiplication

AndresOH — Tue, 11 Oct 2022 20:28:16 GMT

Hello, how would you do this measure if you need to add an if condiction? For example, only multiply if Article Id > 2.