Forum Discussion

CityCat35's avatar
CityCat35
Frequent Visitor
2 years ago
Solved

Table Relationships & Cross-Filter Directions

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.