Forum Discussion
Replicate this SQL in DAX
Hi,
Please can someone help me replicate the below SQL in DAX:
SELECT
c.id AS [c.id]
, c.status AS [c.status]
, c.script_validation_id AS [c.script_validation_id]
, sv.id AS [sv.id]
, ctm.script_validation_id AS [ctm.script_validation_id]
, 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]
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, sv.id, ctm.script_validation_id,
c.correct_marks_opo, c.correct_marks_ssv, c.correct_mark
----------------------------------------------------------------------------------------
This is my DAX attempt so far:
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" }
)
)
Thanks in advance.
11 Replies
is the new table absolutely necessary? I think it may be easier to write new measure that would calculate whatever KPI you would need to get from the calculated table, rather than creating the table itself. Is that an option?
My thinking is to create a table that will hold a number of rows, based on the grouping and joining shown in the SQL, and that table can be filtered by some slicers on the visual.
By creating a measure it would be creating a single KPI where in fact I require a table that can be filtered by column values. Now, I may be wrong but I am thinking I do a table to achieve this. If I am wrong then please make me aware of a better way?
Thanks.the way I see it you should still be able to filter using slicers based on refCORRECTIONS (assuming there is join between it and refCLERICAL_TOTAL_MARK)
The way I see it, there should be 3 measures, we can test with first one to see if behaviour meets your expectations[Old Mark] = CALCULATE ( SUM ( refCLERICAL_TOTAL_MARK[total_mark] ), FILTER ( refCORRECTIONS, refCORRECTIONS[status] IN { "fully_approved", "initial_approval", "suggested" } ) )
