Forum Discussion
Create a measure using relations and performing a multiplication
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?
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] ) )
3 Replies
johnt75Super User
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?
- Jihwan_Kim
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] ) ) Hello, how would you do this measure if you need to add an if condiction? For example, only multiply if Article Id > 2.
