Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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. 

  • Anonymous's avatar
    Anonymous
    7 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's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Anonymous,

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

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable

      Messaged you! Thanks!

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft 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