topic Re: Count Distinct from latest records in DAX Commands and Tips

Count Distinct from latest records

SushmaReddy — Thu, 10 Mar 2022 12:33:26 GMT

Max Date =

Var mat= T1[Mat1]

return

MAXX(

FILTER(ALL(T1),T1[M1]=mat),(T1[Date])) to get max date records

Another Column to get the count of failed records

# Error = IF(And(T1[Date]=T1[Max Date],Single_Material[Status]="Failed"),1,0)

Here comes the problem where i have duplicates for failed records as shown below.

M1 Date Status

123 02-02-2022 Failed

231 02-02-2022 Failed

456 02-02-2022 Failed

456 03-02-2022 Success

I am getting the failed count as 2 but i want it as 2.If the latest record is failed it should not be counted.

Re: Count Distinct from latest records

amitchandak — Thu, 10 Mar 2022 16:03:48 GMT

SushmaReddy , try like

Measure =
VAR __id = MAX ('Table'[M1] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[M1] = __id )
CALCULATE ( Count ('Table'[Status] ), VALUES ('Table'[M1] ),'Table'[M1] = __id,'Table'[Date] = __date, 'Table'[Status] = "Failed" )

Re: Count Distinct from latest records

v-chenwuz-msft — Tue, 15 Mar 2022 06:09:59 GMT

Hi SushmaReddy ,

You can try this code:

Measure = VAR _s = SUMMARIZE ( 'Table', [M1], "max", MAX ( 'Table'[Date] ), "status", CALCULATE ( MAX ( 'Table'[Status] ), FILTER ( 'Table', 'Table'[M1] = EARLIER ( 'Table'[M1] ) ) ) ) RETURN COUNTROWS ( FILTER ( _s, [status] = "Failed" ) )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

Re: Count Distinct from latest records

SushmaReddy — Tue, 02 Aug 2022 08:09:44 GMT

Thank you and the logic is working