Forum Discussion
Solved
Filter tabel by same values in different rows
Can someone help me with a formula for a calculated column that makes it possible to show only records that have two items A and B within the same number?
Example data:
| NR | Item |
| 1 | A |
| 1 | B |
| 2 | A |
| 3 | A |
| 3 | B |
| 4 | A |
| 5 | A |
| 5 | C |
THe result i'm looking for is:
| NR | Item |
| 1 | A |
| 1 | B |
| 3 | A |
| 3 | B |
Here's a column expression that returns T/F for Numbers 1 and 3 rows.
ABonly = VAR items = CALCULATETABLE ( DISTINCT ( T1[Item] ), ALLEXCEPT ( T1, T1[NR] ) ) VAR AB = { "A", "B" } RETURN COUNTROWS ( INTERSECT ( items, AB ) ) = 2 && COUNTROWS ( items ) = 2Pat
1 Reply
Here's a column expression that returns T/F for Numbers 1 and 3 rows.
ABonly = VAR items = CALCULATETABLE ( DISTINCT ( T1[Item] ), ALLEXCEPT ( T1, T1[NR] ) ) VAR AB = { "A", "B" } RETURN COUNTROWS ( INTERSECT ( items, AB ) ) = 2 && COUNTROWS ( items ) = 2Pat