Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |