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/

[LewisSmeby]

I have this exact same question, however, the provided answer does not Exclude, but includes only a few possible values. Let's say that 'Severity' could have any number of other values, other than the limited number of values in this example, how would you exclude one or more values (instead of writing something that only includes the other possibilities, as there are too many to account for in my scenario).

Thank you!

[MikePowerBI]

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