Count with DAX when specific value

[Anonymous]

Greetings,

I need help on how to count data when we have an specific value. Sorry in advance if this has been answered somewhere else, I tried several cases found here but none of them worked for this case.

Our goal: Obtain the Nº of TestCode that have zero Pass_Fail_Error “F” value, 1 or more than 1.

Given a table like this one here.

Testcode nº of F per Testcode

We should get an array like this.

Measure: [nº of TestCode = 0, nº of TestCode = 1, nº of TestCode > 1]

Example:

Measure [1, 1, 2]

______________________________

Or 3 diferent measures

Measure 1: Nº of TestCode that have zero Pass_Fail_Error “F” value                     1

Measure 2: Nº of TestCode that have one Pass_Fail_Error “F” value                       1

Measure 3: Nº of TestCode that have more than 1 Pass_Fail_Error “F” value       2

Does anyone know how to solve this?

[Anonymous · Accepted solution]

HI @Anonymous ,

You can create a variable with summarize function to aggregate records that has Code 'F' and group based on ‘Test Code’ and ‘FPcode’ categoried, and add custom fields to get correspond record count.

formula =
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( Table ), [Fail_Pass_Code] = "F" ),
        [TestCode],
        [Fail_Pass_Code],
        "_Count", COUNTA ( Table[Fail_Pass_Code] )
    )
RETURN
    SUMX ( summary, [_Count] )

Regards,

Xiaoxin Sheng