Count Distinct from latest records

[SushmaReddy]

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

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.

[v-chenwuz-msft · Accepted solution]

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.

[v-chenwuz-msft · Accepted solution]

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.

[SushmaReddy · Accepted solution]

Thank you and the logic is working

[amitchandak]

@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" )