Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
swkuruvatti
Frequent Visitor

count of id for max date with filter

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)

 

IDActionedOnLIDStatus_Check
770000196312/15/23 2:05 AMB9SDPASS
770000275512/10/23 6:17 PMB9SDPASS
770000275512/14/23 2:05 AMB9SDFAIL
770000275512/14/23 4:05 AMB9SDFAIL
770000196312/9/23 1:05 AMB9SDFAIL
770000196312/10/23 1:05 AMB9SDFAIL
770000196312/11/23 1:05 AMB9SDFAIL

 

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)
        )
4 REPLIES 4
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1703225221232.png

 

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.

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

swkuruvatti_0-1703281925954.png

 

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
Super User
Super User

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

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 )

770000275512/14/23 4:05 AMB9SDFAIL

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors