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
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.
Solved! Go to 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)
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:
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
143 | |
111 | |
108 | |
99 | |
95 |