PowerPivot and DAX papers,posts,FAQs,webcasts,samples

I have two tables that I need to relate. The first has a list of student names and their responses to a test broken down by sections of the test. For example:

Student1 Part1
Student1 Part2
Student1 Part3
Student2 Part1
Student2 Part2
Student2 Part3
Student3 Part1
Student3 Part2
Student3 Part3

The second table is a list of classes with enrollment

Class1 Student1
Class1 Student2
Class2 Student1
Class2 Student3
Class3 Student2
Class2 Student3

I tried to related them with a table of unique student names with the hope that I could re-aggregate the scores by class. So the row names would be the class names and the columns would be the test parts:

Part1 Part2 Part3
Class1
Class2
Class3

I can get the scores to aggregate by class or part, but not not both. Any ideas?

Thanks!

Hi Robert,

Assuming that on the first table (StudentPart) you have field Score that you would like to sum and that second table name is ClassStudent, we could define following calculated measure:

=CALCULATE(SUM(StudentPart[Score]), FILTER(VALUES(StudentPart[Student]), COUNTROWS(FILTER(VALUES(ClassStudent[Student]), ClassStudent[Student] = StudentPart[Student]))>0))

This calculation sums Score, but filters records in StudentPart table where exists records in ClassStudent table based on Student column.
As we cannot create joins between 2 tables you provided, we are doing comparison of values in the calculated measure.

Let me know if that is what you are looking for. If no, could you please clarify your source tables - that is add column that you want to aggregate with sample values and then show me what values you would like to see in the report, and I'll try to figure out DAX calculation for that.

My test was done on following tables:

Student Part Score
Student1 Part1 1
Student1 Part2 2
Student1 Part3 3
Student2 Part1 4
Student2 Part2 5
Student2 Part3 6
Student3 Part1 7
Student3 Part2 8
Student3 Part3 9


Class Student
Class1 Student1
Class1 Student2
Class2 Student1
Class2 Student3
Class3 Student2
Class2 Student3


Result was:

TotalScore Column Labels
Row Labels Part1 Part2 Part3 Grand Total
Class1 5 7 9 21
Class2 8 10 12 30
Class3 4 5 6 15
Grand Total 12 15 18 45

This worked like a charm. Thank you very much!!!