Basket Analysis - One slicer on one table

[Anonymous]

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. 

[v-danhe-msft]

Hi @Anonymous,

Could you please post me some sample data and your desired result or share the pbix if possible?

Regards,

Daniel He

[Anonymous]

Messaged you! Thanks!

[v-danhe-msft]

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

[Anonymous]

Hi there @v-danhe-msft

Currently, if I select item A I know that 192 customers have item A and 182 customers have items A & B. etc. 

Now, what I would like to do is be able to select multiple products from the slicer to see how many customers other products related to the items selected in the slicer. Currently, if I select both A & B in the slicer, it shows all the count of customers for each item if the customer has products A OR B.  I need the table to show the count of customers for each item if the customer has products A AND B

Unfortunately, the forums are stuggling with uploading images from an example I made from Excel- I will try to show what I am looking for best I can with this. 

Without anything selected in the slicer the table would look like the following. 

Item                  # of Customers

A                       192

B                       182

C                       75

D                       50

E                       25

With only item A selected in the slicer - It will show the number of customers that have item A & the item in the list. 

Item                  # of Customers

A                       192

B                       125

C                       20

D                       15

E                        5

In this case we know that 192 people have A / 125 customers have A & B, 20 customers have A & C / 15 customers have A & D / and 5 customers have A & E

With items A & C selected in the slicer - It will show the number of customers that have items A& C as well the other items in the list. 

Item                  # of Customers

A                       20

C                       20

B                       5

D                       4

E                        1

In this case we know that 20 people have A&C / 5 customers have A, C, & B /  4 customers have A, C, and D / and 1 had A, C, & E

With items A, C, and D selected in the slicer - It will show the number of customers that have items A. C, & D as well the other items in the list. 

Item                  # of Customers

A                       4 

C                       4

D                       4

E                       1

B                       0

In this case we know that 4 people have A, C, D / 1 customer has A, C, D & E /  0 customers have A, C, D, & B

[Anonymous]

Update: After a bit of research, I am thinking that I need to use an addtable to calculate this properly but I am not really sure how this would work correctly. 

[Anonymous · Accepted solution]

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 )