I need to create a Dynamic Legend in a Pie Chart based on Slicer Selection

[ClausS]

Hi,

I have a Slicer to select a Sales Territory, values are US or EMEA. I have a Pie chart which should show the sales grouped by (using the Legend Field) either Address_Countries, when EMEA is selected or Address_States if US is selected.

Is there any way to use different Table fields in the Pie chart Legend field based on a slicer selection? I don't want to work with Bookmarks.

Some additional Explanation: The Teeritory Slicer is important to slice the data set for the Page, and I'm looking for an elegant solution by NOT introducing a 2nd slicer based on Parameters because when you select US , having a chart slicing you sales by country makes no sense so I automaticly want to show it by State.
Thanks

[Sergii24 · Accepted solution]

Hi @ClausS, I see that you don't want to use a field parameter or bookmark but still want to change values in the legend of a pie chart.

Let's take a look at this example:

As you can see, legend values on the visual depend on a column that is used in "Legend" section when bulding pie chart. This section (i.e. Legend) allows you to select one or multiple columns and even create a hirarchy for you. However, the hierarchy can't be defined dynamically neither a single column can be replaced with another one. Both operations should be done by the report author.

Field parameter works in a specifi manner that allows to "switch" between columns. I'd say that this is "the most elegant" way to achieve the desired result. "Bookmark" can be an alternative, but I won't explain it as you prefer to not use it.

If you really want to avoid both, then you need to work on a data model side: once you select a desired value in a slicer, it should filter another table which contains 2 columns: "slicer value" and "desired legend output". Now you use "desired legend output" as a legend field. However, the outcome of such approach would really depend on your data model (once you do it, something else can broke or not work as expected). I'd definitely reccomend you to avoid any similar complexity when the option with "field parameter" is available.

If you come up with a better solution, let us know so we all can learn! 🙂

[ClausS]

Hi Sergii,

thanks for taking the time to explain the possible solutions in detail. I realized that I need to follow the suggest data model path to make that fully automated since that is what I want. 

Have a nice day

[danextian]

They've said it. Use field paramters. You wil need to rename each field thgouh to EMEA for Address_Countries field and US for Address_States.

[ClausS]

That's not what I want. I don't want a 2nd slicer, I want the legend to switch dynamicly based on the Territory selection. The Parameter Solution is NOT solving the challange I have. I can't just rename the slicer to EMEA because I need the Territory slicer as well for the page.

[nilendraFabric]

Hello @ClausS 

@Fowmy pointed to right direction 

1. Create a field parameter with `Address_Countries` and `Address_States`.
2. Add the generated slicer to your report.
3. Drag the field parameter into the pie chart’s Legend.
4. The slicer will dynamically update the legend based on selection.

hope this helps.

[ClausS]

That's not what I want. I don't want a 2nd slicer, I want the legend to switch dynamicly based on the Territory selection. The Parameter Solution is NOT solving the challange I have.

[Fowmy]

@ClausS 

Try Field Paramters 

[ClausS]

That's not what I want. I don't want a 2nd slicer, I want the Legend to switch dynamicly based on the Territory selection. The Parameter Solution is NOT solving the challange I have.