topic Sum Values with an Intermediate Lookup Table in DAX Commands and Tips

Sum Values with an Intermediate Lookup Table

Anonymous — Thu, 17 Sep 2020 17:15:21 GMT

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!

Re: Sum Values with an Intermediate Lookup Table

CNENFRNL — Thu, 17 Sep 2020 18:48:39 GMT

Hi, Anonymous , 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' )

Re: Sum Values with an Intermediate Lookup Table

Anonymous — Thu, 17 Sep 2020 19:18:41 GMT

Brilliant, thank you!!