Forum Discussion

scoutmedic's avatar
scoutmedic
Helper I
2 years ago
Solved

Counting rows with multiple conditions

Hi all, 

 

I have a table with 2 columns: area and error and need to count rows where area = floor and errors are apple, orange, grapes, and watermelon. I tried using the following which worked well for me in another situation with 2 criteria, but AND doesn't work with multiple criteria:

 

Errors = CALCULATE(COUNTROWS('Error table'),
AND(CONTAINSSTRING('Combined Error Data'[Area], "Floor"), CONTAINSSTRING('Combined Error Data'[Category Detail], "apple")),
AND (CONTAINSSTRING('Combined Error Data'[Category], "Floor"), CONTAINSSTRING('Combined Error Data'[Category Detail], "orange")),
AND (CONTAINSSTRING('Combined Error Data'[Category], "Floor"), CONTAINSSTRING('Combined Error Data'[Category Detail], "grapes")),
AND (CONTAINSSTRING('Combined Error Data'[Category], "Floor"), CONTAINSSTRING('Combined Error Data'[Category Detail], "watermelon"))
 
I tried && in place of the ANDs, but that didn't work either. 
 
I thought about creating measures and trying to combine the, but no luck either.
 
Thoughts on how I can rewrite this?
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    Hi scoutmedic 

     

    Download example PBIX file

     

    You say you have 1 table but your measure refers to 2 : Error Table and Combined Error Data

     

    You are also referring to 3 different columns but you said you have 2 : [Area], [Category] and [Category Detail]

     

    If you are using 1 table with 2 columns like like this

     

     

    then this measure will do what you want

     

    Errors = CALCULATE(COUNTROWS('Combined Error Data'), FILTER('Combined Error Data', CONTAINSSTRING('Combined Error Data'[Category], "Floor") && (CONTAINSSTRING('Combined Error Data'[Category Detail], "apple") || CONTAINSSTRING('Combined Error Data'[Category Detail], "orange") || CONTAINSSTRING('Combined Error Data'[Category Detail], "grapes") || CONTAINSSTRING('Combined Error Data'[Category Detail], "watermelon"))))

     

     

     

    regards

     

    Phil

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    2 years ago

    Hi scoutmedic 

     

    Download example PBIX file

     

    You say you have 1 table but your measure refers to 2 : Error Table and Combined Error Data

     

    You are also referring to 3 different columns but you said you have 2 : [Area], [Category] and [Category Detail]

     

    If you are using 1 table with 2 columns like like this

     

     

    then this measure will do what you want

     

    Errors = CALCULATE(COUNTROWS('Combined Error Data'), FILTER('Combined Error Data', CONTAINSSTRING('Combined Error Data'[Category], "Floor") && (CONTAINSSTRING('Combined Error Data'[Category Detail], "apple") || CONTAINSSTRING('Combined Error Data'[Category Detail], "orange") || CONTAINSSTRING('Combined Error Data'[Category Detail], "grapes") || CONTAINSSTRING('Combined Error Data'[Category Detail], "watermelon"))))

     

     

     

    regards

     

    Phil

    • scoutmedic's avatar
      scoutmedic
      Helper I
      2 years ago

      Hi Phil,

       

      Error Table should be Combined Error Data. Missed renaming that one for the example.

       

      You were spot on with the table. There is other fruit on the floor I don't want to count which is why I was going this route. I'll give it a shot and see what happens.

      • scoutmedic's avatar
        scoutmedic
        Helper I
        2 years ago

        PhilipTreacy This worked. If I wanted to modify it to look for items in floor and hall that were not apple, orange, or grape?