Forum Discussion

RichHead1821's avatar
RichHead1821
Icon for Resolver I rankResolver I
4 years ago
Solved

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

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 "individ...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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

     

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

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