Counting results with different criteria

[Anonymous]

I saw a similar post but I couldn't get the result to work for my data.

I have data that I simplified below. There are multiple serial #s and test results. Some tests pass the first time (#1,2,3,4). Some pass on a retest (#5,8). Some fail a restest (#6,12). Some fail and were not retested (#9,10,11). Some were tested and passed every time (#7). I would also like to get the results in the shown form which I'm sure is easy but I'm still learning

Serial #	Test result
1	pass
2	pass
3	pass
4	pass
5	fail
5	fail
5	pass
6	fail
6	fail
7	pass
7	pass
8	fail
8	pass
9	fail
10	fail
11	fail
12	fail
12	fail
12	fail
12	fail
pass first time	pass restest	fail retest	fail no retest	pass multiple tests
4	2	2	3	1

[ValtteriN · Accepted solution]

Hi,

You can create this kind of category measure to solve this issue:

Test Categories =

SWITCH(TRUE(),

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])))=1

&& MAX('Table (7)'[Test result])="pass",

"Pass first time",

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="fail"))>0 &&

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="pass"))>0,

"Pass retest",

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="fail"))=0&&

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="pass"))>0,

"Pass all",

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="fail"))>1&&

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="pass"))=0,

"Fail retest",

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="fail"))>0&&

COUNTROWS(filter(all('Table (7)'),'Table (7)'[Serial #]=MAX('Table (7)'[Serial #])&&'Table (7)'[Test result]="pass"))=0,

"No retest")

Table (7) is just the test data.

Then create additional table like this:

Create this kind of measure:

Test counts = sumx('Table (7)',if(MAX('test categories'[Value])=[Test Categories],1,0))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/