Replicate this SQL in DAX

Hi Stachu

Thanks for your response. I followed your example and got a number I was expecting but I feel I need to show more screenshots to demonstrate my trouble faced more clearly.

All data used is not real-world.

The SQL I'm looking to replicate is:

SELECT
          c.id AS [c.id]
          , c.status AS [c.status]
          , c.script_validation_id AS [c.script_validation_id]
          , c.correct_marks_opo
          , c.correct_marks_ssv
          , c.correct_mark
          , c.examiner_number_opo
          , c.examiner_number_ssv
          , c.examiner_number
          , SUM( ctm.total_mark ) AS [ctm.old mark]
          , CASE
                   WHEN c.status = 'fully_approved' THEN c.correct_marks_opo
                   WHEN c.status = 'initial_approval' THEN c.correct_marks_ssv
                   WHEN c.status = 'suggested' THEN c.correct_mark
                   ELSE '0'
           END AS [c.new mark/mark override]
          , CASE
                   WHEN c.status = 'fully_approved' THEN c.examiner_number_opo
                   WHEN c.status = 'initial_approval' THEN c.examiner_number_ssv
                   WHEN c.status = 'suggested' THEN c.examiner_number
                   ELSE '0'
           END AS [c.examiner override]
FROM CORRECTIONS AS c
INNER JOIN SCRIPT_VALIDATION AS sv
     ON c.script_validation_id = sv.id
INNER JOIN CLERICAL_TOTAL_MARK AS ctm
     ON sv.id = ctm.script_validation_id
WHERE c.status IN ( 'fully_approved', 'initial_approval', 'suggested' )
GROUP BY c.id, c.status, c.script_validation_id, c.correct_marks_opo, c.correct_marks_ssv
     , c.correct_mark, c.examiner_number_opo, c.examiner_number_ssv, c.examiner_number
     , c.correct_marks_opo, c.correct_marks_ssv, c.correct_mark

The above SQL returns the following dataset:

So I'm trying to replicate the above SQL in DAX and produce the same dataset.

Below is my attempt to replicate the SQL using DAX:

tblApprovedErrors =
               CALCULATETABLE (
               SUMMARIZE (
                                 refCORRECTIONS,
                                 refCORRECTIONS[id],
                                 refCORRECTIONS[status],
                                 refCORRECTIONS[script_validation_id],
                                 refCORRECTIONS[correct_marks_opo],
                                 refCORRECTIONS[correct_marks_ssv],
                                 refCORRECTIONS[correct_mark],
                                 refCORRECTIONS[examiner_number_opo],
                                 refCORRECTIONS[examiner_number_ssv],
                                 refCORRECTIONS[examiner_number],
                                 "Old Mark", SUMX (
                                          RELATEDTABLE ( refCLERICAL_TOTAL_MARK ),
                                          refCLERICAL_TOTAL_MARK[total_mark]
                                              ),
                                 "New Mark/Mark Override", SWITCH (
                                                                         refCORRECTIONS[status],
                                                                         "fully_approved", refCORRECTIONS[correct_marks_opo],
                                                                         "initial_approval", refCORRECTIONS[correct_marks_ssv],
                                                                         "suggested", refCORRECTIONS[correct_mark],
                                                                         BLANK ()
                                                                         ),
                                  "Examiner Override", SWITCH (
                                                              refCORRECTIONS[Status],
                                                              "fully_approved", refCORRECTIONS[examiner_number_opo],
                                                              "initial_approval", refCORRECTIONS[examiner_number_ssv],
                                                              "suggested", refCORRECTIONS[examiner_number],
                                                              BLANK ()
                                                              )
                                  ),
          FILTER (
                     refCORRECTIONS,
                     refCORRECTIONS[status] IN { "fully_approved", "initial_approval", "suggested" }
                     )
         )

THe above DAX returns the dataset:

As you can see the DAX version is bringing in two additional rows ( IDs 11 and 12 ). Why is this?  Can someone please inform me what I am doing wrong in my DAX as it's not the same as the SQL?

To note, the CORRECTIONS table joins to the SCRIPT_VALIDATION table and this table joins to the CLERICAL_TOTAL_MARKS table.

Thanks in advance.