count of id for max date with filter

[swkuruvatti]

I am looking for DAX where count of ID's from filter with colmn = Pass and Fail actioned on = maxdate

Pass for ID = 1 (7700001963)

Fail for ID = 1 (7700002755)

ID	ActionedOn	LID	Status_Check
7700001963	12/15/23 2:05 AM	B9SD	PASS
7700002755	12/10/23 6:17 PM	B9SD	PASS
7700002755	12/14/23 2:05 AM	B9SD	FAIL
7700002755	12/14/23 4:05 AM	B9SD	FAIL
7700001963	12/9/23 1:05 AM	B9SD	FAIL
7700001963	12/10/23 1:05 AM	B9SD	FAIL
7700001963	12/11/23 1:05 AM	B9SD	FAIL

This giving me zero result: 

Count_FAIL =

VAR _seldate =

    MAX ( 'OL_ongoing'[ActionedOn].[Date])

RETURN

    CALCULATE (

        DISTINCTCOUNT ( 'OL_ongoing'[ID] ),

        FILTER (ALLSELECTED ( 'OL_ongoing')

                ,'OL_ongoing'[Status_Check] = "FAIL" && 'OL_ongoing'[ActionedOn].[Date] = _seldate)

        )

[v-xinruzhu-msft]

Hi @swkuruvatti 

You can refer to the following solution.

Create a measure

Measure =
VAR a =
    SUMMARIZE (
        SUMMARIZE ( ALLSELECTED ( 'Table' ), [ID], "Max", MAX ( 'Table'[ActionedOn] ) ),
        [Max]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER ( 'Table', [ActionedOn] IN a )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[swkuruvatti]

thank you but somehow it's not correct - I am getting which still has duplicate counts

when i perform SQlL - i get Pass = 2908 and Fail = 238

SELECT temp.*
FROM Table temp
INNER JOIN
(SELECT ID, MAX(ActionedOn) AS MaxDateTime
FROM Table
GROUP BY ID) groupedtt
ON temp.ID = groupedtt.ID
AND temp.ActionedOn = groupedtt.MaxDateTime
where STATUS = 'PASS'
and ActionedOn >= '2023-12-06'
order by ID desc

[jennratten]

Hello - this is returning 0 results because the status of the record with the max date is PASS.  Are you looking for something different?

[swkuruvatti]

I need to count all the ID's with Fail reasults. but if you see for the ID '7700002755' has max date of 'Fail' so am expecting at least 1 count. (I hope i explained )

7700002755

12/14/23 4:05 AM

B9SD

FAIL