CODEWITHSUNDEEP
powerbidaxmeasure
Artgene Tablatin
Artgene Tablatin

Reputation: 21

Tagging as trained and certified

I have list of employee ids who are required to complete all 4 courses. I have an identifier column which is trained and contains 1 if the course is completed. Now I need to tag them as certified if they have completed all courses and the score for their course 1 >= 80.

User

employe_id Course_name final_score Status Trained
10 course_1 85 completed 1
10 course_2 80 completed 1
10 course_3 90 completed 1
10 course_4 95 completed 1
20 course_1 70 completed 1
20 course_2 80 subscribed 1
20 course_3 97 subscribed 1
20 course_4 60 completed 1

In this case I should only get 1. How can I do it in a measure?

I tried several countrows measure however when I validated the data I get some who have a passing score in course 1 but not completed the other 3.

Upvotes: 2

Views: 46

Answers (2)

Marcus
Marcus

Reputation: 4005

You can do this without any changes to your data model. First create a measure which identifies individuals as certified or not:

Is Certified =
VAR _score_course1 =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[final_score] ),
        'Table'[Course_name] = "course_1"
    )
VAR _incomplete =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status] <> "completed" )
RETURN
    SWITCH (
        TRUE (),
        NOT HASONEVALUE ( 'Table'[employee_id] ), BLANK (),
        _incomplete = 0 && _score_course1 >= 80, "Certified",
        "Not certified"
    )

Then, when the measure framework for establishing individuals as certified is in place, add a new measure with simple iteration over employee_id VALUES to determine a proper count:

Certified Count = 
SUMX ( 
    VALUES ( 'Table'[employee_id] ) , 
    IF ( [Is Certified] = "Certified" , 1 )
)

The result here, with the first measure in a simple table visual, and the second measure in a card:

enter image description here

Upvotes: 1

Dordi
Dordi

Reputation: 778

What you can do is a Group BY in M, the idea is to create to column so that you can compare the sum of all your scores and the number of courses taken:

= Table.Group(#"YOUR_TABLE", {"employe_id"}, {{"Sum", each List.Sum([final_score]), type nullable number}, {"Count", each Table.RowCount(_), Int64.Type}})

now we can compare Sum and count in DAX (create a new column):

Status = IF('Table'[Sum] >= 80* 'Table'[Count],"completed","Failed")

You can also use a measure, a query would be like:

Status = IF(sum('Your Table'[final_score]) >= 80 * COUNT('Your Table'[Status]),"Completed","Failed")

Hope this helps

Upvotes: 1

Related Questions