Forum Discussion
dynamic legend based on slicer selection - possible?
- 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.
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.
Hi,
Thanks for reply , detailed explanation and pbix
works great.