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 123 AAAAA 2 07/13/2020 221 B...
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.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Anonymous - One thought on this, use buttons instead of slicers for your "vision". Then you could leverage stacked visuals and bookmarks.

MFelix's avatar
MFelix
Super User
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.