Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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!
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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
Hi @po
This also works but you are duplicating the vendors part in the measure since one vendor comes from the slicer table and other from the vendors table.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Thanks for update
Hi,
looks like if add VALUES(VENDOR_ID) works o.k. not sure if this is best way to do it?
Thanks
Hi,
Thanks for reply , detailed explanation and pbix
works great.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |