Solved: Sum Values with an Intermediate Lookup Table

sharonhuston · ‎09-17-2020

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 · ‎09-17-2020

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' )

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

CNENFRNL · ‎09-17-2020

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' )

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

sharonhuston · ‎09-17-2020

Brilliant, thank you!!

Sum Values with an Intermediate Lookup Table

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024