Counting rows with multiple conditions

[scoutmedic]

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?

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[PhilipTreacy · Accepted solution]

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]

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]

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