Table Relationships & Cross-Filter Directions

[CityCat35]

I teach both elementary and high school-aged students. I have 3 tables. The first two are the individual grades for each student for each course I teach: 

	ELEM
SARAH	MATH	95
BILLY	SCIENCE	70
BILLY	MATH	88
SARAH	SCIENCE	72
SARAH	MATH	88
BILLY	SCIENCE	90
BILLY	MATH	80
SARAH	SCIENCE	87

	HIGH SCHOOL
JASON	HISTORY	66
MOLLY	CALCULUS	88
MOLLY	HISTORY	95
JASON	CALCULUS	77
JASON	HISTORY	91
MOLLY	CALCULUS	70
MOLLY	HISTORY	90
JASON	CALCULUS	80

The third table is the grade cutpoints for each course.

	GRADES	Min	Maz	Letter
ELEM	MATH	87	100	A
ELEM	MATH	75	86	B
ELEM	MATH	62	74	C
ELEM	MATH	44	61	D
ELEM	MATH	0	43	F
ELEM	SCIENCE	83	100	A
ELEM	SCIENCE	76	82	B
ELEM	SCIENCE	66	81	C
ELEM	SCIENCE	54	65	D
ELEM	SCIENCE	0	53	F
HIGH SCHOOL	HISTORY	85	100	A
HIGH SCHOOL	HISTORY	75	84	B
HIGH SCHOOL	HISTORY	62	74	C
HIGH SCHOOL	HISTORY	44	61	D
HIGH SCHOOL	HISTORY	0	43	F
HIGH SCHOOL	CALCULUS	86	100	A
HIGH SCHOOL	CALCULUS	74	85	B
HIGH SCHOOL	CALCULUS	61	73	C
HIGH SCHOOL	CALCULUS	42	60	D
HIGH SCHOOL	CALCULUS	0	41	F

I would like to create a matrix for each group (one for elem and one for high school) that shows the average test results by student/course, and I also want to automatically show the letter grade based on the average results. Thanks to @hnguy71, I created a separate "Courses" table to bridge the grade and score tables. Additionally, the relationship between the High School Student Score table and the new "Courses" table has a cross-filter direction-, but the data displayed for the Elementary Schools students is incorrect. I've been playing around with the relationships for a while, but I can only get one table at a time to filter properly? Any thoughts on how to make this work?

[v-jiewu-msft · Accepted solution]

Hi @CityCat35 ,

Based on my testing, please try the following methods again:

1.Create the new measure to show the letter grade based on the average results.

elem school Grade = 
VAR _Number = [Elem school average]
RETURN
CALCULATE(MAX('Third Table'[Letter]), _Number >= 'Third Table'[Min] && _Number <= 'Third Table'[Maz] && 'Third Table'[School] = "ELEM")

High school letter = 
VAR _Number = [High school average]
RETURN
CALCULATE(MAX('Third Table'[Letter]), _Number >= 'Third Table'[Min] && _Number <= 'Third Table'[Maz] && 'Third Table'[School] = "HIGH SCHOOL")

2.Drag the measure into the matrix visual.

3.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tristanbrown]

Hello!

I can't diagnose the specific issue you're referring to without more details, plus I'm on a train so can't build it out myself, but I can offer some recommendations that might help you achieve your goals.

First, consider re-modeling the data you're working with. To enable effective communication between the three fact tables, you need dimension tables for the columns you're interested in aggregating.

Here's a step-by-step approach:

1. **Consolidate Tables**: Combine the "High School" and "ELEM" tables into a single table, which we'll call "FactGrades". Add a new column, "School Type", to designate whether a row is for a High School or Elementary School student. This consolidation will centralize all student grades into one table.

2. **Create Dimension Table**: Create a new table called "DimCourses" with two columns: "School_Type" and "Course_Name". This table will serve as a bridge between your fact tables.

3. **Establish Relationships**: Create two many-to-one relationships:
- One between "FactGrades" and "DimCourses".
- Another between "FactGrades" and "FactCourses" (ensure "FactCourses" includes the letter grade ranges).

These relationships should be based on the "Course_Name" column.

4. **Build Your Matrix**: With both fact tables connected via a single dimension table, you can now populate a matrix with your data. Add a slicer to filter the data by "School_Type" and "Course_Name" from the "DimCourses" table.

5. **Create a Measure for Letter Grades**: Define a measure to associate a letter grade with a student's grade based on specified ranges.

DAX:

Letter Grade =
VAR _studentGrade = SELECTEDVALUE(FactStudents[grade])
RETURN
CALCULATE(
MIN(FactCourses[Letter]),
FILTER(
ALL(FactCourses),
_studentGrade > FactCourses[Min_Grade] && _studentGrade <= FactCourses[Max_Grade]
)
)

By adding this measure to your matrix, it will automatically calculate the letter grade regardless of the hierarchy level (by student, course, etc.).

These changes should help streamline your data model and enhance the functionality of your reports.