Forum Discussion

po's avatar
po
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

dynamic legend based on slicer selection - possible?

Hi,   Have a visual (map) and have slicer whereby  can view sales and put dimension in legend (e.g. customer or product)   Rather than duplicate the visual to say see sales qty by customer in leg...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi po ,

     

    I have answered to a similar post a few days here is the data I used and the solution but adjusted to your needs

     

    SALES:

    PRODUCT_ID   VENDOR_ID   QUANTITY(€)   DATE
    123 AAAAA 2 07/13/2020
    221 BBBBB 5

    		 07/13/2020

     

    PRODUCTS:

    PRODUCT_ID   CATEGORY   SUBCATEGORY  
    123 SPORTS FOOTBALL
    221 SLEEP SHOES

     

     

    VENDOR: 

    VENDOR_ID   SHOP_TYPE   LOCATION  
    AAAAA BIG ITALY
    BBBBB SUPERMARKET GERMANY

     

    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 =
    SELECTEDVALUE ( 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 and the column of values on the legend as you can see legend is dinamic.

     

    Check PBIX file attach.

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi po ,

 

I have answered to a similar post a few days here is the data I used and the solution but adjusted to your needs

 

SALES:

PRODUCT_ID   VENDOR_ID   QUANTITY(€)   DATE
123 AAAAA 2 07/13/2020
221 BBBBB 5

 07/13/2020

 

PRODUCTS:

PRODUCT_ID   CATEGORY   SUBCATEGORY  
123 SPORTS FOOTBALL
221 SLEEP SHOES

 

 

VENDOR: 

VENDOR_ID   SHOP_TYPE   LOCATION  
AAAAA BIG ITALY
BBBBB SUPERMARKET GERMANY

 

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 =
    SELECTEDVALUE ( 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 and the column of values on the legend as you can see legend is dinamic.

 

Check PBIX file attach.

 

  • po's avatar
    po
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Hi,

     

    Thanks for reply , detailed explanation and pbix

     

    works great.

     
  • po's avatar
    po
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Hi,

     

    Found and issue which seem to be encountering both on your example and one I'm working on.

     

    If select slicer e.g. to show by vendor and then try apply filter on the vendor itself graph doesn't chnage to limit only to the filtered items. e.g.. in exmaple below woudl only expect value AAAAA.. to show 

     

    Any thoughts on how we can get the graph to also show only the filtered values when select that in slicer and apply a filetr to the same item.

     

    • po's avatar
      po
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Hi, 

       

      looks like if add VALUES(VENDOR_ID)  works o.k. not sure if this is best way to do it?

       

      Thanks

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi po ,

       

      To filter out the column of the slicers for customer or product you need to select the columns from the slicer table and not from the vendor tables.

       

      The slicer for products / vendors works in a disconnected way with the measure so the selection needs to be made on the disconnected table.

       

      Add a slicer with the column Value from the Slicer table and you will understand how it works.

       

      If you need any further assistance please tell me.

      • po's avatar
        po
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        Hi,

         

        Thanks for reply and explanation.

         

        Also, looks like it works if add bit in bold say want to have slicer on vendor or filter on vendor 

        SWITCH (
        SelectedType ,
        "Vendor", CALCULATE ( SUM ( Sales[QUANTITY(€) ] ), Vendors[VENDOR_ID ] IN Selected_Value, VALUES(Vendors[VENDOR_ID ])),
         
        Thanks