Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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")
)
)
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