Suppose we have 2 tables as follows
Grade1
| Subject | Marks |
|---|---|
| Maths | 90 |
| Physics | 80 |
| Chemistry | 70 |
Grade2
| Subject | Marks |
|---|---|
| Maths | 100 |
| Physics | 88 |
| Chemistry | 90 |
OUR DESIRED RESULT IS A CALCULATED TABLE USING DAX with output as follows
Grades_1&2
| Subject | Marks |
|---|---|
| Chemistry | 160 |
| Physics | 168 |
| Maths | 190 |
We can use a Variable to combine the 2 tables first using UNION function in DAX and then summarize the Subjects using Summarize function
BUT
the problem is how can we aggregate the Marks.. The Aggregate functions like SUM,MAX,MIN,AVERAGE are not available for VARIABLES
The answer is we have to use an ITERATOR. Fortunately the beautiful “Summarize” function provides not only FILTER context but also ROW context to apply the ITERATOR functions
The final and correct formula looks like this
Grades_1&2 =
VAR temp =
UNION ( Grade1, Grade2 )
RETURN
SUMMARIZE (
temp,
[Subject],
“Marks”, SUMX ( FILTER ( temp, [Subject] = EARLIER ( [Subject] ) ), [Marks] )
)
Download the file from here