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 legned and and sales qty by product in legend want to have a slicer whereby user can select either product or customer and legend wil change dynamically.

Can get the slicer part but believe legend needs to be a dimension.

How can this be best achieved?

Thanks

Need Help

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.
dynamic.pbix29 KB

Product	Sports
Product	Sleep
Vendor	AAAAA
Vendor	BBBBB

9 Replies

MFelix
Super 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.

dynamic.pbix29 KB
- po
  Post Prodigy
  6 years ago
  Hi,
  
  Thanks for reply , detailed explanation and pbix
  
  works great.
- po
  Post 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
    Post 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
v-diye-msft
Community Support
6 years ago
Hi po

if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!