How to Duplicate SQL Inner Join Logic to Power BI (Same Table)

[nocapcart]

Hello, 

My company stores historical data in a data warehouse where each row in the main "fact" table is a behavior that one of our customers has. For instance, we have individuals that purchase products from our company and each purchase is a separate row denoting the detailed information about that purchase. The problem arises when I want to compare products of the same customer as they are rows and unrelated to one another. If I want to grab all customers that purchase product A and see if they also purchased product B, because they are on separate rows product A filters out product B. Realistically a row needs to become a column and the logic for doing that in SQL is inner joining to the same table. In power bi the only fix I have found is to create two separate tables one filtered for product A and another filtered for Product B and establish a relationship between them based on our customer ID. This essentially duplicates information found in the original fact table and due to the nature of calculated tables, makes handling the report cumbersome (long publish times, slow to open, etc..). Are there any better solutions to this issue? 

Thanks. 

[latimeria · Accepted solution]

Hi @nocapcart ,

This is basket analysis. Not a simple problem.
You can find information here Basket analysis – DAX Patterns or here Power BI: Basket Analysis Full Tutorial - Finance BI (finance-bi.com)

[nocapcart]

Thank you, I appreciate the information. Not something I've done before but first time for everything. Cheers 

[sevenhills]

Basket analysis: 

if you are looking for basket analysis, after checking out the @latimeria links, you can also do "Disconnected tables" method for doing Basket analysis, assuming you have star schema type of model:

• Create Disconnected tables:
  Create a copy of slicer table for all slicers you need and make sure they are not connected in your model! Say, Products Slicer, Customers Slicer table.
  In Power Query, you can create as reference. Most importating thing is disconnect them in the model view. 
  In DAX, you can create the table as Products Slicer = Products. Most important thing is disconnect this slicer tables in the model view.
• Add a measure per your needs

Say, 

Sales = 
//get list of all customers of selected products
VAR __isProductFiltered = ISFILTERED ( 'Products Slicer'[Product] ) //is product has filter
VAR __isCustomerFiltered = ISFILTERED ( 'Customers Slicer'[Customer] ) //is customer has filter
VAR __isNothingSelected = NOT __isProductFiltered && NOT __isCustomerFiltered //is both customer and product are not selected
VAR __getProducts = ( NOT ( __isCustomerFiltered && NOT __isProductFiltered ) ) || __isNothingSelected 
VAR __getCustomers = ( NOT ( __isProductFiltered && NOT __isCustomerFiltered ) ) || __isNothingSelected
VAR __customersforSelectedProduct = 
//IF ( __getProducts,
    SUMMARIZE ( 
        FILTER ( 
            Sales,
            Sales[ProductKey] IN VALUES ( 'Products Slicer'[ProductKey] ) 
        ),
        Sales[ProductKey],
        Sales[CustomerKey]
    )
//get list of all products of selected customers
VAR __productsforSelectedCustomer = 
    SUMMARIZE ( 
        FILTER ( 
            Sales,
            Sales[CustomerKey] IN VALUES ( 'Customers Slicer'[CustomerKey] ) 
        ),
        Sales[ProductKey],
        Sales[CustomerKey]
    )

//combine both table and get the distinct customers and product 
VAR __filterSales = 
DISTINCT (
    UNION ( 
        FILTER ( __customersforSelectedProduct, __getProducts ),
        FILTER ( __productsforSelectedCustomer, __getCustomers )
    )
)
RETURN
//filter sales for cutomers and products
CALCULATE ( 
    SUM ( Sales[Total Sales Amount] ), 
    TREATAS ( __filterSales, Sales[ProductKey], Sales[CustomerKey] ) 
)

explained in this link...

https://perytus.com/2021/03/23/multiple-slicers-use-or-condition-to-visualize-data/

[nocapcart]

Really great information. Thanks