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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for your answer, the link is great, but it's only working with one table and applying filters to it. In my case, I have multiple tables, and PowerBI should show info from one table or other.

    • 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.