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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nocapcart
New Member

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

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. 

1 ACCEPTED SOLUTION
latimeria
Solution Specialist
Solution Specialist

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)

 

View solution in original post

4 REPLIES 4
latimeria
Solution Specialist
Solution Specialist

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)

 

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

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/

Really great information. Thanks

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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