Forum Discussion
Basket Analysis - One slicer on one table
Hello All,
I am a long time reader - First time poster.
I have been working on a report where I am using a basket analysis. I have been using this link on "Basket Analysis" and it has been very helpful in generating the initial data that I am using. I have been requested to be able to further breakdown the data.
Currently, the report has a slicer with all of the products and a table with all of the products and the number of customers with that product. If an item is chosen in the slicer it shows all of the products and number of customers with that product. I.E. If I chose product A it says 10 people have A and 8 people have A & B, etc.
I am being asked is to be able to select, multiple products on the one slicer that will update the table. At present, if multiple products are being select it only shows the product with the most customers and does not update the list. As more prducts are selected, in therory, the number of customers who have the product should go down.
In other words, if Item A is selected in the slicer and it shows 76 people have A, 73 with B, 42 with C, and 10 with D - If products A & B are chosen in the slicer, would show 73 customers with A, 73 customers with B, x customers with C, x customers with D
I have three tables.
Table 1: Lists customers and products
Customer: Product:
1 a
2 b
3 a
3 c
Etc.
Table 2: Lists a product list
Table 3: is the same as Table 2 but is being used as a filter.
Thanks in advance for the help.
- Anonymous7 years ago
Thanks a million to OwenAuger - He built an amazing solution to my problem.. Here is the Dax he created!
Associated Applications = VAR FilterApplicationsAllSelected = ALLSELECTED ( 'Filter Applications'[Application] ) VAR Companies = FILTER ( CALCULATETABLE ( SUMMARIZE ( 'Complete Table', 'Complete Table'[Companies] ), ALL ( 'List Applications' ), USERELATIONSHIP ( 'Complete Table'[Application], 'Filter Applications'[Application] ) ), VAR ApplicationsForCurrentCompany = CALCULATETABLE ( SUMMARIZE ( 'Complete Table', 'List Applications'[Application] ), ALL ( 'List Applications' ) ) RETURN // This condition tests whether the current Company has all FilterApplications ISEMPTY ( EXCEPT ( FilterApplicationsAllSelected, ApplicationsForCurrentCompany ) ) ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Complete Table'[Companies] ), Companies )
6 Replies
- v-danhe-msft
Microsoft Employee
Hi Anonymous,
Could you please post me some sample data and your desired result or share the pbix if possible?
Regards,
Daniel He
- AnonymousNot applicable
Messaged you! Thanks!
- v-danhe-msft
Microsoft Employee
Hi Anonymous,
From your description, I could not understand the sentence"if Item A is selected in the slicer and it shows 76 people have A, 73 with B, 42 with C, and 10 with D - If products A & B are chosen in the slicer, would show 73 customers with A, 73 customers with B, x customers with C, x customers with D".
If I select the item A, the other columns should all related with A and should not related other items right? Could you please let me konw the logic of your data and post your desired result?
Regards,
Daniel He