Forum Discussion
Possible Group By required - how?
Okay, so I've resolved it myself.
So to help others, here is what I did...
1) In Power Query, I created a conditional column and assigned 1 to every status value that was not 'validated' or 'validated'.
2) In DAX, I wrote:
tblScriptPacketsCompleted = FILTER(
SUMMARIZE(
refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[packet_id],
"Values", SUM(refSCRIPT_VALIDATION[ValidatedORValidated_Rejected])
),
[Values] = 0
)3) mScriptPacketsCompleted = COUNT( tblScriptPacketsCompleted[packet_id] )
The measure in Step 3 had the answer I assigned to my card visual. Easy isn't it... when you know how ;-)
Hope this helps others.
To help explain my requirements more, I have produced the working SQL to acheive what I'm looking for. See below:
SELECT p.id
FROM PACKET AS p
INNER JOIN SCRIPT_VALIDATION sv
ON p.id IN
( SELECT sv.packet_id
FROM SCRIPT_VALIDATION AS sv
GROUP BY sv.packet_id
HAVING SUM(IIF( status <> 'validated' AND status <> 'validated_rejected', 1, 0)) = 0
)
GROUP BY p.id
ORDER BY p.id
How do I acheive the same in DAX?
Thanks.
Okay, so I've resolved it myself.
So to help others, here is what I did...
1) In Power Query, I created a conditional column and assigned 1 to every status value that was not 'validated' or 'validated'.
2) In DAX, I wrote:
tblScriptPacketsCompleted = FILTER(
SUMMARIZE(
refSCRIPT_VALIDATION,
refSCRIPT_VALIDATION[packet_id],
"Values", SUM(refSCRIPT_VALIDATION[ValidatedORValidated_Rejected])
),
[Values] = 0
)
3) mScriptPacketsCompleted = COUNT( tblScriptPacketsCompleted[packet_id] )
The measure in Step 3 had the answer I assigned to my card visual. Easy isn't it... when you know how ;-)
Hope this helps others.