Excluding values from distinct count based on another column

[MikePowerBI]

Hi there, 

I am looking to create a measure that counts the unique values of column "Batch" if the "Severity" column equals BLANK, Minor, or Event while also excluding the value from the count if the "Severity" column equals Major or Critical. For example based on the table below, the measure should equal 4 because it includes batches A, C, D, H, while excluding batches B and G. 

I tried using the formula: 

= CALCULATE(DISTINCTCOUNT('Test Data'[Batch]), FILTER('Test Data', 'Test Data'[Severity] = "Event" || 'Test Data'[Severity] = "Minor" || 'Test Data'[Severity] = BLANK()), FILTER('Test Data', 'Test Data'[Severity] <> "Major"), FILTER('Test Data', 'Test Data'[Severity] <> "Critical"))

but it equals 6, so it does not appear to be excluding batches B and G as I intended. 

Any help with this would be greatly appreciated!

[VahidDM · Accepted solution]

Hi @MikePowerBI 

Try this measure:

Measure =
VAR _A =
    CALCULATETABLE (
        VALUES ( 'Table'[Batch] ),
        FILTER ( 'Table', 'Table'[Severity] IN { "", "Minor", "Event" } )
    )
VAR _B =
    CALCULATETABLE (
        VALUES ( 'Table'[Batch] ),
        FILTER ( 'Table', 'Table'[Severity] IN { "Major", "Critical" } )
    )
VAR _C =
    EXCEPT ( _A, _B )
RETURN
    COUNTROWS ( _C )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[MikePowerBI]

Hi VahidDM. This worked great, thank you for your prompt reply!