cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 )

View solution in original post

6 REPLIES 6
v-danhe-msft
Microsoft
Microsoft

Hi @Anonymous,

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Messaged you! Thanks!

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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 )

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors