Sum Values with an Intermediate Lookup Table

[sharonhuston]

I have two tables, Courses and Lessons.  A course can have muliple lessons.  A lesson can be in many courses.  Because of this, I have a lookup table (~Lookup_Couse-Lessons) with a 1:Many relationship on each side.  I need a measure for a visual that will calculate the total Course Duration by summing the Duration in Hours for each Lesson within a course.

I'm new to DAX, and confused.  I've spent a few days on this, playing with SUMX, FILTER, CALCULATE, and more.  I'm pulling my hair out, and would really appreciate any help!

[CNENFRNL · Accepted solution]

Hi, @sharonhuston , I assume you use a matrix with Courses[Course] in row field, you may try such a measure to sum up durations of lessons composed in corresponding courses.

Sum Duration =
CALCULATE ( SUM ( Lessons[Duration in Hours] ), '~Lookup_Couse-Lessons' )

[sharonhuston]

Brilliant, thank you!!