How would you approach this requirement (akin to SQL left outer join to same table)

[RichHead1821]

Hello,

I have a requirement to be able to identify rows in a table where there is another row for the same ID with different data. Using an example to explain:

Each row in the table has an "individual ID", a "purchased product ID" and a set of product attributes ("product", "product type", "product sub type", "product term").

I need to be able to flag an 'individual' if they have > 1 "purchased product ID" where:

1. The "product is not the same across any two rows, or
2. The "product" is the same but the "product type", "product sub type" or "product term" is not the same across any two rows.

If I were using SQL I would use a left outer join to the same table, on "individual ID" with relevant WHERE clauses to check the rules and any non-null "individual ID" from the join would be marked as eligible.

How would you go about this? 

Is there a way to do this in MQuery?

Is this possible in DAX?

[RichHead1821]

Thank you Paul, I shall pore over this to understand what it's doing, but looking at your screenshots it looks like a solution. I really appreciate your time and help.
Rich

[PaulDBrown · Accepted solution]

See if this measure works:

Filter INdivd ID =
VAR Details =
    CALCULATE (
        COUNTROWS (
            SUMMARIZE (
                'Table',
                'Table'[Product Sub-type],
                'Table'[Product Term],
                'Table'[Product Type]
            )
        ),
        ALLEXCEPT ( 'Table', 'Table'[Individ ID], 'Table'[Product ID] )
    )
VAR Prods =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Product ID] ),
        ALLEXCEPT ( 'Table', 'Table'[Individ ID] )
    )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Table'[Individ ID] ),
            FILTER ( 'Table', Details > 1 || Prods > 1 )
        )
    )

I've attached the sample PBIX file