DAX - Measures to count distinct status and group by max date and multiple fields or group key

[joulethrex]

I am trying to write multiple DAX expressions to summarize a transaction dataset. The combination of [Manufacturer] and [PartNo] will determine uniqueness so those fields need to be grouped together. I can also concatenate the 2 fields together and create a composite key ([ManufacturerPartKey]) if its easier grouping data that way.

MyTable sample data:

 Manufacturer  PartNo     TxDate     TxStatus      ErrorCode     ManufacturerPartKey
 ABC             123    9/29/2020   Fail         CodeInvalid     ABC-123
 ABC             123    9/30/2020   Fail         CodeInvalid     ABC-123
 ABC             123    10/1/2020   Success      null            ABC-123
 ABC             789    10/1/2020   Fail         PartInvalid     ABC-789   
 ABC             567    10/1/2020   Success      null            ABC-567
 XYZ             567    9/29/2020   Fail         LoadFail        XYZ-567
 XYZ             567    9/30/2020   Fail         LoadFail        XYZ-567
 XYZ             789    10/1/2020   Fail         APIFault        XYZ-789
 LMO             456    9/29/2020   Fail         APIFault        LMO-456
 LMO             456    9/30/2020   Fail         APIFault        LMO-456
 EFG             123    10/1/2020   Success      null            EFG-123
 QRS             123    10/1/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/2/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/3/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/4/2020   Fail         PartInvalid     QRS-123
 QRS             567    10/4/2020   Success      null            QRS-567

Requirements:

1. Count of distinct combinations of Manufacturer + Part No

    Expected Output = 9

2. Count of Success

    Expected Output = 4

3. Count of "outstanding" failures by [Manufacturer] and [PartNo] or [ManufacturerPartKey] and max TxDate

    Expected Output = 5

Answer:

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

      4. Count of reprocessed (ABC-123 was submitted 3 times and was re-processed on the 3rd attempt)

        1

      5. Count of distinct error codes by max TxDate

    ErrorCode        Count
    CodeInvalid       0    
    APIFault          2
    LoadFail          1
    PartInvalid       2
    Null              4

Expected Summary Table Output:

Manufacturer TotalSubmissions  TotalFailed  TotalSucceeded  DistinctPartNoCount  OutstandingFailures ReprocessedCount 
 ABC           5                3           2               3                    1                   1
 XYZ           3                3           0               2                    2                   0
 LMO           2                2           0               1                    2                   0
 EFG           1                0           1               1                    0                   0
 QRS           5                4           1               2                    0                   0

[V-lianl-msft · Accepted solution]

Hi @joulethrex ,

Based on your description, you have calculated the value of "outstanding failures", and I have calculated other values in the sample file. If "outbound failures" you get the wrong value, please inform the calculation logic to see if we can help you.

Sample .pbix

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

[joulethrex · Accepted solution]

DAX - Measures to count distinct status and group by max date and multiple fields or group key

30m ago

I am trying to write multiple DAX expressions to summarize a transaction dataset. The combination of [Manufacturer] and [PartNo] will determine uniqueness so those fields need to be grouped together. I can also concatenate the 2 fields together and create a composite key ([ManufacturerPartKey]) if its easier grouping data that way.

MyTable sample data:

 Manufacturer  PartNo     TxDate     TxStatus      ErrorCode     ManufacturerPartKey
 ABC             123    9/29/2020   Fail         CodeInvalid     ABC-123
 ABC             123    9/30/2020   Fail         CodeInvalid     ABC-123
 ABC             123    10/1/2020   Success      null            ABC-123
 ABC             789    10/1/2020   Fail         PartInvalid     ABC-789   
 ABC             567    10/1/2020   Success      null            ABC-567
 XYZ             567    9/29/2020   Fail         LoadFail        XYZ-567
 XYZ             567    9/30/2020   Fail         LoadFail        XYZ-567
 XYZ             789    10/1/2020   Fail         APIFault        XYZ-789
 LMO             456    9/29/2020   Fail         APIFault        LMO-456
 LMO             456    9/30/2020   Fail         APIFault        LMO-456
 EFG             123    10/1/2020   Success      null            EFG-123
 QRS             123    10/1/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/2/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/3/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/4/2020   Fail         PartInvalid     QRS-123
 QRS             567    10/4/2020   Success      null            QRS-567

Requirements:

1. Count of distinct combinations of Manufacturer + Part No

    Expected Output = 9

2. Count of Success

    Expected Output = 4

3. Count of "outstanding" failures by [Manufacturer] and [PartNo] or [ManufacturerPartKey] and max TxDate

    Expected Output = 5

Answer:

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

      4. Count of reprocessed (ABC-123 was submitted 3 times and was re-processed on the 3rd attempt)

        1

      5. Count of distinct error codes by max TxDate

    ErrorCode        Count
    CodeInvalid       0    
    APIFault          2
    LoadFail          1
    PartInvalid       2
    Null              4

Expected Summary Table Output:

Manufacturer TotalSubmissions  TotalFailed  TotalSucceeded  DistinctPartNoCount  OutstandingFailures ReprocessedCount 
 ABC           5                3           2               3                    1                   1
 XYZ           3                3           0               2                    2                   0
 LMO           2                2           0               1                    2                   0
 EFG           1                0           1               1                    0                   0
 QRS           5                4           1               2                    0                   0

[joulethrex · Accepted solution]

I am trying to write multiple DAX expressions to summarize a transaction dataset. The combination of [Manufacturer] and [PartNo] will determine uniqueness so those fields need to be grouped together. I can also concatenate the 2 fields together and create a composite key ([ManufacturerPartKey]) if its easier grouping data that way.

MyTable sample data:

 Manufacturer  PartNo     TxDate     TxStatus      ErrorCode     ManufacturerPartKey
 ABC             123    9/29/2020   Fail         CodeInvalid     ABC-123
 ABC             123    9/30/2020   Fail         CodeInvalid     ABC-123
 ABC             123    10/1/2020   Success      null            ABC-123
 ABC             789    10/1/2020   Fail         PartInvalid     ABC-789   
 ABC             567    10/1/2020   Success      null            ABC-567
 XYZ             567    9/29/2020   Fail         LoadFail        XYZ-567
 XYZ             567    9/30/2020   Fail         LoadFail        XYZ-567
 XYZ             789    10/1/2020   Fail         APIFault        XYZ-789
 LMO             456    9/29/2020   Fail         APIFault        LMO-456
 LMO             456    9/30/2020   Fail         APIFault        LMO-456
 EFG             123    10/1/2020   Success      null            EFG-123
 QRS             123    10/1/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/2/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/3/2020   Fail         PartInvalid     QRS-123
 QRS             123    10/4/2020   Fail         PartInvalid     QRS-123
 QRS             567    10/4/2020   Success      null            QRS-567

Requirements:

1. Count of distinct combinations of Manufacturer + Part No

    Expected Output = 9

2. Count of Success

    Expected Output = 4

3. Count of "outstanding" failures by [Manufacturer] and [PartNo] or [ManufacturerPartKey] and max TxDate

    Expected Output = 5

Answer:

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

      4. Count of reprocessed (ABC-123 was submitted 3 times and was re-processed on the 3rd attempt)

        1

      5. Count of distinct error codes by max TxDate

    ErrorCode        Count
    CodeInvalid       0    
    APIFault          2
    LoadFail          1
    PartInvalid       2
    Null              4

Expected Summary Table Output:

Manufacturer TotalSubmissions  TotalFailed  TotalSucceeded  DistinctPartNoCount  OutstandingFailures ReprocessedCount 
 ABC           5                3           2               3                    1                   1
 XYZ           3                3           0               2                    2                   0
 LMO           2                2           0               1                    2                   0
 EFG           1                0           1               1                    0                   0
 QRS           5                4           1               2                    0                   0

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  I cannot understand the last 2 measures.

Hope this helps.

[V-lianl-msft · Accepted solution]

Hi @joulethrex ,

Based on your description, you have calculated the value of "outstanding failures", and I have calculated other values in the sample file. If "outbound failures" you get the wrong value, please inform the calculation logic to see if we can help you.

Sample .pbix

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

[joulethrex]

Hello @V-lianl-msft 

Thanks for your help. For the RANK measure I am receiving the following error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist". Do you know a workaround for this? I suspect it might be because I am working off DEV data so there are many duplicate records that I'm unable to clean at the moment.

[V-lianl-msft]

Hi @joulethrex ,

It should be clarified that "rank" is a calculated column.

DAX measure are based on row context so any given value from slicers, filters, interactions with other visualizations, variables, relationships,visual fields and so on can influence your result.

Based on your scenario, if measure is used and manufacturer is used as the primary key of table visual, the correct rank cannot be calculated in table visual.

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