Filter to keep rows where two specific values are both present

[tgjones43]

Hello all

I have the following data in a table visual and would like to create a measure I can use to filter the table so that only Site 4 is displayed i.e. records where there is a Method value for both A and B, but not A only, and not any other combination of Method values. Is this possible?

Thank you.

Site	Method
1	A
2	A
2	C
3	A
4	A
4	B
5	A
5	B
5	C

[az38]

@tgjones43 

try to use a measure to filter by TRUE like

Measure = 
IF(
CALCULATE(DISTINCTCOUNT(Table[Method]), ALLEXCEPT(Table, Table[Site]), Table[Method] IN {"A", "B"}) = 2,
TRUE(),
FALSE()
)

[tgjones43]

Thank you @az38, that formula works nicely. But how do I filter on TRUE? I was hoping to be able to filter my table to show only the true values, but when I move the measure to the visual level filter it's not possible to select anything.

[az38]

@tgjones43 

You also can create a calculated column with the same formula and place it to filter. It should work

[tgjones43]

That works nicely, thank you @az38 

Finally, how would I add another condition to that formula? I would like to apply this to the dataset where only the the years = 2017, 2018 and 2019. There is a related table with a [YEAR] column.

[az38]

@tgjones43
Try a construction like
IF(
CALCULATE(DISTINCTCOUNT(Table[Method]), ALLEXCEPT(Table, Table[Site]), Table[Method] IN {"A", "B"}, Table2[Year] IN {“2017”, “2018”, “2019”}) = 2,
TRUE(),
FALSE()
)

[tgjones43]

Thank you @az38, that solution works perfectly.