Filter rows because "additional column" contains value

[PetyrBaelish]

With data that looks similar to this ...

Username --- Group

John Smith -- GroupA

John Smith -- GroupC

Jane Doe -- GroupB

Jane Doe -- GroupC

Mike Brown -- GroupC

Mike Brown -- GroupD

I'm trying to filter out all rows where the user is a member of GroupA or GroupB. So on the example above I would expect rows 1-4 - that is both entries for John Smith and Jane Doe to be filtered out (even the ones for "GroupC"). I would expect rows 5 & 6 to still appear on the report as 2 separate rows).

I've tried to get the right formula, but can't figure out how to filter out rows 2 and 4.

[AlB · Accepted solution]

Hi @PetyrBaelish 

You could create a new calculated table:

NewTable =
FILTER (
    Table1;
    ISEMPTY (
        INTERSECT (
            CALCULATETABLE (
                DISTINCT ( Table1[Group] );
                Table1[Username] = EARLIER ( Table1[Username] );
                ALL ( Table1 )
            );
            { "GroupA"; "GroupB" }
        )
    )
)

Another option, depending on what you want,  is to use a table visual with Username and Group in values (with "Don't summarize") and then create a measure as indicated below. Then place the new measure in visual level filters of the table visual and select 'Show items when the value is' --> 1    

ShowMeasure =
IF (
    ISEMPTY (
        INTERSECT (
            CALCULATETABLE ( DISTINCT ( Table1[Group] ); ALL ( Table1[Group] ) );
            { "GroupA"; "GroupB" }
        )
    );
    1
)

[PetyrBaelish]

Thanks - I went with the measure, which works perfectly.