Calculate with multiple tables

[JMWDBA]

So I am pulling data via our Amazon Redshift Server.  There are a total of 9 tables.  Below are each of the tables by name and the fields by table that I am wondering if there is a way to pull into 1 calculated table.  I established relationships between all tables but cant seem to use a field from one table presented with a field from another table into a visual.  What I am trying to do is take the grade on assignments and create a weighted average across all course sections.  I wish I could attach the pbix but because of FERPA Law and the information containing actual student information I cannot. 

id from account_dim is linked to account_id under course_dim (one to many cardinality with a cross filter of both)

then course_id under course_dim is linked in a one to many cardinality with a cross filter direction of both to the course_id field in the tables  , course_section_dim, assignment_dim, assignment_fact, enrollment_dim, score_fact, submission_fact, and user_dim. the user_id field under enrollment_dim is linked to the id field under user_dim.

If I can't bring these fields into one table, what is the best way to be able to use them in one visual.  Example, I would use

1. Table Name: account_dim
   1. Field Name: id
   2. Field Name: name
   3. Field Name: parent_name
   4. Field Name: subaccount1
   5. Field Name: subaccount2
   6. Field Name: canvas_id
2. Table Name: course_dim
   1. Field Name: course_id
   2. Field Name: course_name
   3. Field Name: course_code
   4. Field Name: canvas_id
3. Table Name: course_section_dim
   1. Field Name: course_id
   2. Field Name: course_section_name
   3. Field Name: course_section_id
   4. Field Name: canvas_id
   5. Field Name: Sis Source Id
4. Table Name: assignment_dim
   1. Field Name: assignment_id
   2. Field Name: course_id
   3. Field Name: assignment_title
   4. Field Name: assignment_possible_points
   5. Field Name: canvas_id
5. Table Name: assignment_fact
   1. Field Name: assignment_id
   2. Field Name: course_id
   3. Field Name: assignment_possible_points
6. Table Name: enrollment_dim
   1. Field Name: enrollment_id
   2. Field Name: course_section_id
   3. Field Name: course_id
   4. Field Name: user_id
   5. Field Name: type
   6. Field Name: canvas_id
7. Table Name: score_fact
   1. Field Name: course_id
   2. Field Name: enrollment_id
   3. Field Name: current_score
   4. Field Name: final_score
8. Table Name: submission_fact
   1. Field Name: assignment_id
   2. Field Name: course_id
   3. Field Name: enrollment_term_id
   4. Field Name: user_id
   5. Field Name: score
   6. Field Name: published_score
9. Table Name: user_dim
   1. Field Name: user_id
   2. Field Name: student_name
   3. Field Name: workflow_state
   4. Field Name: canvas_id

Also, how would I convert the below into DAX worthy formulas (this field comes from the course_section_dem table):

To define Academic Term 

• TRIM( SPLIT( [Sis Source Id (Course Section Dim)], "_", 1 ) )

To define Course Number

• TRIM( SPLIT( [Sis Source Id (Course Section Dim)], "_", 4 ) )

To get Course Location

• TRIM( SPLIT( [Sis Source Id (Course Section Dim)], "_", 6 ) )

To get Course Prefix

• TRIM( SPLIT( [Sis Source Id (Course Section Dim)], "_", 3 ) )

To get Course Section

• TRIM( SPLIT( [Sis Source Id (Course Section Dim)], "_", 5 ) )

To get Course Session

• TRIM( SPLIT( [Sis Source Id (Course Section Dim)], "_", 2 ) )

[GilbertQ]

Hi @JMWDBA

You should be able to bring in the related fields if your relationships are working as expected.

What it could possibly be to me is if the data types are not the same between your _dim tables and your _fact tables. So make sure that they have the same data types?

As well as what I would do is to not create the relationships via the auto detection. But rather create them manually.

For example Score Fact

1. Field Name: course_id - Map directly to course_id on course_dim
2. Field Name: enrollment_id - Map directly to enrollment_id on enrollment_dim
3. Field Name: current_score 
4. Field Name: final_score

As well as when doing the mapping to make sure that the Cross-Filter direction is set to single. As that could potentially yield incorrect results.

[v-shex-msft]

HI @JMWDBA,

>>Also, how would I convert the below into DAX worthy formulas (this field comes from the course_section_dem table):

You can refer to below link to know more about how to use split function:

DAX: How to Split (left) a text column on Character (space)?

Regads,

Xiaoxin Sheng