Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RichHead1821
Resolver I
Resolver I

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 "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?

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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 )
        )
    )

Captura de pantalla 2022-06-08 123046.png

 

I've attached the sample PBIX file

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
RichHead1821
Resolver I
Resolver I

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
Community Champion
Community Champion

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 )
        )
    )

Captura de pantalla 2022-06-08 123046.png

 

I've attached the sample PBIX file

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.