Need help creating a DAX measure to count distinct status by groupkey and max date

Question

I am trying to write a couple DAX expressions to count the number of [Status] by [GroupKey] and maximum [TxDate]. For example, the end result should only be counting the below records with the max TxDate and group by GroupKey to show that [CountOfFailures] = 3 and [CountOfSuccess] = 2.

TxDate          Status  GroupKey
9/29/2020 0:00  Failure ABC
9/29/2020 0:00  Failure ABC
9/29/2020 0:00  Failure ABC
9/30/2020 0:00  Success ABC
9/25/2020 0:00  Failure XYZ
9/27/2020 0:00  Failure XYZ
9/28/2020 0:00  Failure XYZ
9/29/2020 10:00 Failure XYZ
9/29/2020 13:00 Failure XYZ
9/29/2020 15:00 Failure XYZ
9/26/2020 0:00  Success EFG
9/27/2020 0:00  Success EFG
9/28/2020 0:00  Success EFG
9/28/2020 0:00  Failure LMN
9/29/2020 0:00  Failure LMN
9/30/2020 22:00 Failure LMN
9/26/2020 0:00  Failure QRS
9/30/2020 00:00 Failure QRS

Currently my DAX expression for counting failures counts ALL of the failures but again my requirement is to count Status and group by the max date and GroupKey.

 NumberOfFailures =

 VAR noOfFailureTransactions=

 CALCULATE(COUNTROWS(MyTable), FILTER(MyTable, MyTable[Status] = "Failure"))
 RETURN

 IF ( ISBLANK( noOfFailureTransactions), 0, noOfFailureTransactions
)

Any help would be greatly appreciated.

msta42a msta42a · Accepted Answer · 2020-11-04T11:33:02

My solution for that:

CountFailure =
VAR _maxDate =
    TREATAS (
        ADDCOLUMNS (
            VALUES ( MyTable[GroupKey] ),
            "maxDate", CALCULATE ( MAX ( 'MyTable'[TxDate] ) )
        ),
        MyTable[GroupKey],
        MyTable[TxDate]
    )
RETURN
    CALCULATE (
        COUNTROWS ( SUMMARIZECOLUMNS ( MyTable[GroupKey], MyTable[Status], _maxDate ) ),
        MyTable[Status] = "Failure"
    )

And second measure:

CountSuccess =
VAR _maxDate =
    TREATAS (
        ADDCOLUMNS (
            VALUES ( MyTable[GroupKey] ),
            "maxDate", CALCULATE ( MAX ( 'MyTable'[TxDate] ) )
        ),
        MyTable[GroupKey],
        MyTable[TxDate]
    )
RETURN
    CALCULATE (
        COUNTROWS ( SUMMARIZECOLUMNS ( MyTable[GroupKey], MyTable[Status], _maxDate ) ),
        MyTable[Status] = "Success"
    )

Need help creating a DAX measure to count distinct status by groupkey and max date

1 Answers