Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic multiple slicers

Hi everyone!

 

I'm stuck with the following matter: 

 

In my model I have 3 tables:

 

MODEL:

 

SALES:

PRODUCT_ID  VENDOR_ID  QUANTITY(€)  DATE
123AAAAA207/13/2020
221BBBBB5

07/13/2020

 

PRODUCTS:

PRODUCT_ID  CATEGORY  SUBCATEGORY  
123SPORTSFOOTBALL
221SLEEPSHOES

 

 

VENDOR: 

VENDOR_ID  SHOP_TYPE  LOCATION  
AAAAABIGITALY
BBBBBSUPERMARKETGERMANY

 

These tables (vendor and products) are connected by ID to sales table. 

 

 

WHAT I WANT TO DO?

I would like to have in the report, two slicers.

1) To select the "vision" that you want: Product or vendor. (Select slicer in the picture)

2) Select details, i.e: Product selected, and  the category of the product. (PRODUCT slicer in the picture)
Example: 


 

 

 

 

 

 

 

 

 

If I change to vendor, it should change insted of showing products (sleep and sports) I should be able to select "SHOP_TYPE"

Many thanks for your help! 🙂

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Create a table with both tables you need:

     

    Type        Value

    Product Sports
    Product Sleep
    Vendor AAAAA
    Vendor BBBBB

     

    Now do the following measure:

    Sum Sales =
VAR SelectedType =
    SELECTEDVALUES ( Slicer[Type] )
VAR Selected_Value =
    VALUES ( Slicer[Value] )
RETURN
    SWITCH (
        SelectedType ;
        "Vendor"; CALCULATE ( SUM ( Sales[QUANTITY(€)  ] ); Vendors[VENDOR_ID  ] IN Selected_Value);
        CALCULATE ( SUM ( Sales[QUANTITY(€)  ] ); Products[CATEGORY  ] IN Selected_Value)
    )

     

    Now use the measure as your values on the charts as you can see in attach file altough I am selecting on products and vendor one of the visualizations is made on subcategory.

     

5 Replies