Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zenton
Helper II
Helper II

Count for all sections of courses completed

 

Hi,

Need assistance with the following

3 tables Names, Courses and Data.

Data contains all records of courses completed for each Person

Courses contains grouping of Skills completed. If Fred has completed Course A, B and C he has then completed Skill S1. If Fred has also completed Coureses D and E but not F Skill S2 is not completed.

 

I got a part the way there with the following Calculated Column. This only works if they only complete one Skill set. If they complete extra courses this does not work. As you can see for Jim

 

All Complete =
  VAR __Table = SUMMARIZE(FILTER('Data',[Name]=EARLIER([Name])),[Skill])
RETURN
SWITCH(
    TRUE(),
    MAXX(__Table,[Skill])="S1" && COUNTROWS(__Table)=1, "S1 Completed",
    MAXX(__Table,[Skill])="S2" && COUNTROWS(__Table)=1, "S2 Completed",
    MAXX(__Table,[Skill])="S3" && COUNTROWS(__Table)=1, "S3 Completed",
    MAXX(__Table,[Skill])="S4" && COUNTROWS(__Table)=1, "S4 Completed",
    MAXX(__Table,[Skill])="S5" && COUNTROWS(__Table)=1, "S5 Completed",
    MAXX(__Table,[Skill])="S6" && COUNTROWS(__Table)=1, "S6 Completed",
"Not Completed"
)

 

zenton_1-1666910442016.png      zenton_2-1666910457973.png     zenton_3-1666911216188.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@zenton , A new measure

 

new measure =
var _count = calculate(Countrows(Summarize(Data, Courses[Skill])), allexcept(Data,Names[Name],Courses[Skill]))
var _all calculate(Countrows(Summarize(Courses, Courses[Skill])), allexcept(Courses,Courses[Skill]))
return
if(_count = _all , max(Courses[Skill]) & " is completed" , "Not completed")

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@zenton , A new measure

 

new measure =
var _count = calculate(Countrows(Summarize(Data, Courses[Skill])), allexcept(Data,Names[Name],Courses[Skill]))
var _all calculate(Countrows(Summarize(Courses, Courses[Skill])), allexcept(Courses,Courses[Skill]))
return
if(_count = _all , max(Courses[Skill]) & " is completed" , "Not completed")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.