Count Groups that contain at least one of a certain value in another column

[jdelmonico]

I have different tests grouped together, and I want to count the number of groups where one or more tests has failed. I don't have access to manipulate the query or add columns, so I am trying to do this in DAX. My data looks like this:

Group	Test	Test Result
Group 1	Test 1	Fail
Group 1	Test 2	Pass
Group 2	Test 1	Pass
Group 2	Test 2	Pass
Group 3	Test 1	Fail
Group 3	Test 2	Fail

So for my outcome I essentially want:

Groups with Failed Tests: 2

Groups with No Failed Tests: 1

Thank you very much!

[tamerj1 · Accepted solution]

Hi @jdelmonico 

please try

Fail Count =
SUMX (
VALUES ( 'Table'[Group] ),
INT ( "Fail" IN CALCULATETABLE ( VALUES ( 'Table'[Test Result] ) ) )
)

Not Fail Count =
SUMX (
VALUES ( 'Table'[Group] ),
INT ( NOT ( "Fail" IN CALCULATETABLE ( VALUES ( 'Table'[Test Result] ) ) ) )
)

[jdelmonico]

Thank you for the suggestion! It might work, but I'm not sure--when I tried it I got an error that it took too much memory! It's a very large dataset.

[jdelmonico]

Nevermind! I had a typo in there that was throwing the calculation off! I have fixed it and this appears to work!