Forum Discussion

MikePowerBI's avatar
MikePowerBI
Icon for Helper II rankHelper II
4 years ago
Solved

Excluding values from distinct count based on another column

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!
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

3 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      MikePowerBI
      Icon for Helper II rankHelper II
      4 years ago

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

    • LewisSmeby's avatar
      LewisSmeby
      New Member
      2 years ago

      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!