Forum Discussion
How to write this SQL query in DAX?
Hi,
Please can someone provide the DAX equivalent for the below SQL query? Thanks in advance.
SELECT sv.packet_id
, sv.syllabus
, sv.session_id
, COUNT(candidate_number) AS CountOfCandidates
, SUM(
CASE sv.status
WHEN 'validated' THEN 1
WHEN 'validated_rejected' THEN 1
ELSE 0
END
) AS Completed
, SUM(
CASE sv.status
WHEN 'validated' THEN 0
WHEN 'validated_rejected' THEN 0
ELSE 1
END
) AS ToBeCompleted
FROM SCRIPT_VALIDATION AS sv
GROUP BY sv.packet_id, sv.syllabus, sv.session_id
Thanks. That put me in the right direction.
In case anyone is interested to know how what the DAX equivalent for the SQL was, see below. I had slightly altered my requirement so the SQL and DAX won't be 100% matching but close enough for you to follow.tblPacketsWithCandidatesBySession = SUMMARIZE(
refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[session_id],
refSCRIPT_VALIDATION[syllabus],
refSCRIPT_VALIDATION[component],
refSCRIPT_VALIDATION[packet_id],
"CountOfCandidates", COUNT( refSCRIPT_VALIDATION[id] ),
"CompletedCandidates", CALCULATE(
COUNT( refSCRIPT_VALIDATION[id] ),
FILTER( refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[status] = "validated"
|| refSCRIPT_VALIDATION[status] = "validated_rejected")
),
"ToBeCompletedCandidates", CALCULATE(
COUNT( refSCRIPT_VALIDATION[id] ),
FILTER( refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[status] <> "validated"
&& refSCRIPT_VALIDATION[status] <> "validated_rejected")
)
)hey !! please accept the solution provided,if it helped you !
Thanks
3 Replies
https://www.wiseowl.co.uk/blog/s2480/dax-sql.htm
i hope this helps !!
Thanks. That put me in the right direction.
In case anyone is interested to know how what the DAX equivalent for the SQL was, see below. I had slightly altered my requirement so the SQL and DAX won't be 100% matching but close enough for you to follow.tblPacketsWithCandidatesBySession = SUMMARIZE(
refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[session_id],
refSCRIPT_VALIDATION[syllabus],
refSCRIPT_VALIDATION[component],
refSCRIPT_VALIDATION[packet_id],
"CountOfCandidates", COUNT( refSCRIPT_VALIDATION[id] ),
"CompletedCandidates", CALCULATE(
COUNT( refSCRIPT_VALIDATION[id] ),
FILTER( refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[status] = "validated"
|| refSCRIPT_VALIDATION[status] = "validated_rejected")
),
"ToBeCompletedCandidates", CALCULATE(
COUNT( refSCRIPT_VALIDATION[id] ),
FILTER( refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[status] <> "validated"
&& refSCRIPT_VALIDATION[status] <> "validated_rejected")
)
)hey !! please accept the solution provided,if it helped you !
Thanks