Forum Discussion
Replicate this SQL in DAX
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" } ) )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_markThe 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.