Forum Discussion

SushmaReddy's avatar
SushmaReddy
Icon for Helper I rankHelper I
4 years ago
Solved

Count Distinct from latest records

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's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

3 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      SushmaReddy
      Icon for Helper I rankHelper I
      4 years ago

      Thank you and the logic is working

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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