Filter to keep rows where two specific values are both present

Question

Hello all&nbsp;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?&nbsp;Thank you.&nbsp;SiteMethod1A2A2C3A4A4B5A5B5C&nbsp;

az38 · Answer

tgjones43&nbsp;
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()
)
&nbsp;

az38 · Answer

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

az38 · Answer

tgjones43Try 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 · Answer

Thank you&nbsp;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.

tgjones43 · Answer

That works nicely, thank you&nbsp;az38&nbsp;&nbsp;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.

Forum Discussion

Filter to keep rows where two specific values are both present

6 Replies

Related content

How to search specific word in table filter Calculations

Make measure ignore specific filter

Specific Filter

Filtering specific visuals

Specific matrix