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 wel...
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

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?