The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Dynamically change legend on Shape map based o...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dynamically change legend on Shape map based on selections in slicers

07-16-2024
07:59 PM

I have a table called Data with many columns. I have setup the dashboard so far so that changes on the slicers in this way:

1. Slicer1: Variant will choose a column from Data corresponding to variant

2. Slicer2: Group, Measure_Name - allows the user to select which of the particular numeric columns in the Data table to display on the graphs. I did this using a bit of an ugly work-around with SWITCH.

3. Slicer3: Attribute, Value, Country - will show the particular regions (countries)

So far my line graph responds nicely to changes in any of the slicers and the shape map responds to changes in the regions. See below.

My raw Data table looks like this:

What im trying to do is change the Legend of the Shape Map so that depending on my selections in each of the 3 slicers I will get a table of |Countries (corresponding to slicer 3)|Average of Metric (corresponding to slicer 2)| which I hope I will be able to get appropriate shading in the Shape Map.

I've tried the DAX code below to create the table but dont seem to be getting anywhere - really any help with be HUGELY appreciated please as Im really new to Power BI.

table2 =

VAR _DS0FilterTable0 = SELECTEDVALUE('Data'[Variant])

VAR _DS0FilterTable1 = SELECTEDVALUE('Measure_Selection1'[Group])

VAR _DS0FilterTable2 = SELECTEDVALUE('Measure_Selection1'[Data_Field])

VAR _DS0FilterTable3 = SELECTEDVALUE('Data'[Year])

// Ensure _DS0FilterTable0 is defined correctly

VAR _SelectedYears = VALUES('Data'[Year])

VAR _MaxYear = MAXX(

FILTER(ALL('Data'[Year]), 'Data'[Year] IN SELECTCOLUMNS(SUMMARIZE(_SelectedYears, 'Data'[Year]), "Year", 'Data'[Year])),

'Data'[Year]

)

VAR _MinYear = MINX(

FILTER(ALL('Data'[Year]), 'Data'[Year] IN SELECTCOLUMNS(SUMMARIZE(_SelectedYears, 'Data'[Year]), "Year", 'Data'[Year])),

'Data'[Year]

)

// Define selected metric based on user selection

VAR _Selected_Metric =

IF(

HASONEVALUE(Measure_Selection1[Measure_Name]),

SWITCH(

VALUES(Measure_Selection1[Measure_Name]),

"Tot_Pop_1Jan_000s",SUM(Data[Tot_Pop_1Jan_000s]),"Tot_Pop_1Jul_000s",SUM(Data[Tot_Pop_1Jul_000s]),"Male_Pop_1Jul_000s",SUM(Data[Male_Pop_1Jul_000s]),"Female_Pop_1Jul_000s",SUM(Data[Female_Pop_1Jul_000s]),"Pop_Density",SUM(Data[Pop_Density]),"Pop_Sex_Ratio",SUM(Data[Pop_Sex_Ratio]),"Median_Age",SUM(Data[Median_Age]),"Nat_Change",SUM(Data[Nat_Change]),"Nat_Change_Rate",SUM(Data[Nat_Change_Rate]),"Pop_Change_000s",SUM(Data[Pop_Change_000s]),"Pop_Growth_Rate",SUM(Data[Pop_Growth_Rate]),"Births_000s",SUM(Data[Births_000s]),"Births_Women15_19_000s",SUM(Data[Births_Women15_19_000s]),"Crude_Birth_Rate",SUM(Data[Crude_Birth_Rate]),"Tot_Fertility_Rate",SUM(Data[Tot_Fertility_Rate]),"Net_Production_Rate",SUM(Data[Net_Production_Rate]),"Mean_Childbearing_Age",SUM(Data[Mean_Childbearing_Age]),"Pop_Sex_Ratio_At_Birth",SUM(Data[Pop_Sex_Ratio_At_Birth]),"Tot_Deaths_000s",SUM(Data[Tot_Deaths_000s]),"Male_Deaths_000s",SUM(Data[Male_Deaths_000s]),"Female_Deaths_000s",SUM(Data[Female_Deaths_000s]),"Crude_Death_Rate",SUM(Data[Crude_Death_Rate]),"Life_Exp_At_Birth",SUM(Data[Life_Exp_At_Birth]),"Male_Life_Exp_At_Birth",SUM(Data[Male_Life_Exp_At_Birth]),"Female_Life_Exp_At_Birth",SUM(Data[Female_Life_Exp_At_Birth]),"Life_Exp_At_15Yrs",SUM(Data[Life_Exp_At_15Yrs]),"Male_Life_Exp_At_15Yrs",SUM(Data[Male_Life_Exp_At_15Yrs]),"Female_Life_Exp_At_15Yrs",SUM(Data[Female_Life_Exp_At_15Yrs]),"Life_Exp_At_65Yrs",SUM(Data[Life_Exp_At_65Yrs]),"Male_Life_Exp_At_65Yrs",SUM(Data[Male_Life_Exp_At_65Yrs]),"Female_Life_Exp_At_65Yrs",SUM(Data[Female_Life_Exp_At_65Yrs]),"Life_Exp_At_80Yrs",SUM(Data[Life_Exp_At_80Yrs]),"Male_Life_Exp_At_80Yrs",SUM(Data[Male_Life_Exp_At_80Yrs]),"Female_Life_Exp_At_80Yrs",SUM(Data[Female_Life_Exp_At_80Yrs]),"Infant_Deaths_Under1Yr_000s",SUM(Data[Infant_Deaths_Under1Yr_000s]),"Infant_Mortality_Rate",SUM(Data[Infant_Mortality_Rate]),"Births_Surviving_1Yr",SUM(Data[Births_Surviving_1Yr]),"Deaths_Under_5Yrs_000s",SUM(Data[Deaths_Under_5Yrs_000s]),"Mortality_Under_5Yrs",SUM(Data[Mortality_Under_5Yrs]),"Mortality_Under_40Yrs",SUM(Data[Mortality_Under_40Yrs]),"Male_Mortality_Under_40Yrs",SUM(Data[Male_Mortality_Under_40Yrs]),"Female_Mortality_Under_40Yrs",SUM(Data[Female_Mortality_Under_40Yrs]),"Mortality_Under_60Yrs",SUM(Data[Mortality_Under_60Yrs]),"Male_Mortality_Under_60Yrs",SUM(Data[Male_Mortality_Under_60Yrs]),"Female_Mortality_Under_60Yrs",SUM(Data[Female_Mortality_Under_60Yrs]),"Mortality_Under_15_50Yrs",SUM(Data[Mortality_Under_15_50Yrs]),"Male_Mortality_Under_15_50Yrs",SUM(Data[Male_Mortality_Under_15_50Yrs]),"Female_Mortality_Under_15_50Yrs",SUM(Data[Female_Mortality_Under_15_50Yrs]),"Mortality_Under_15_60Yrs",SUM(Data[Mortality_Under_15_60Yrs]),"Male_Mortality_Under_15_60Yrs",SUM(Data[Male_Mortality_Under_15_60Yrs]),"Female_Mortality_Under_15_60Yrs",SUM(Data[Female_Mortality_Under_15_60Yrs]),"Net_Migrants_000s",SUM(Data[Net_Migrants_000s]),"Net_Migration_Rate",SUM(Data[Net_Migration_Rate]),blank()

)

)

// Calculate average of selected metric for each region/subregion

VAR Avg_Metric_Table =

SUMMARIZE(

FILTER(

ALL('Data'),

'Data'[Variant] = _DS0FilterTable0 && 'Data'[Year] >= _MinYear && 'Data'[Year] <= _MaxYear

),

'Data'[Region, subregion, country or area *],

"Average_Selected_Metric", AVERAGEX(VALUES('Data'[Region, subregion, country or area *]), _Selected_Metric)

)

RETURN

Avg_Metric_Table

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-16-2024
10:16 PM

@AnzBhy , Are you using field Parameters? because when you use a field parameter, It will change the title when change the measure or axis slicer

A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...

Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE

Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

Microsoft Power BI Learning Resources, 2023 !!

Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-17-2024
02:44 PM

Hi, appreciate you getting back to me. I am able to get the slicer of the measures as below:

The above gives me this slicer which allows me to select the column I want to use in the line graph via:

I then use SWITCH to select what values to display in the line graph as below:

Selected_Metric_Sum =

IF(HASONEVALUE(Measure_Selections[Measure_Name]),

SWITCH(VALUES(Measure_Selections[Measure_Name]),

"Tot_Pop_1Jan_000s",SUM(Data[Tot_Pop_1Jan_000s]),"Tot_Pop_1Jul_000s",SUM(Data[Tot_Pop_1Jul_000s]),"Male_Pop_1Jul_000s",SUM(Data[Male_Pop_1Jul_000s]),"Female_Pop_1Jul_000s",SUM(Data[Female_Pop_1Jul_000s]),"Pop_Density",SUM(Data[Pop_Density]),"Pop_Sex_Ratio",SUM(Data[Pop_Sex_Ratio]),"Median_Age",SUM(Data[Median_Age]),"Nat_Change",SUM(Data[Nat_Change]),"Nat_Change_Rate",SUM(Data[Nat_Change_Rate]),"Pop_Change_000s",SUM(Data[Pop_Change_000s]),"Pop_Growth_Rate",SUM(Data[Pop_Growth_Rate]),"Births_000s",SUM(Data[Births_000s]),"Births_Women15_19_000s",SUM(Data[Births_Women15_19_000s]),"Crude_Birth_Rate",SUM(Data[Crude_Birth_Rate]),"Tot_Fertility_Rate",SUM(Data[Tot_Fertility_Rate]),"Net_Production_Rate",SUM(Data[Net_Production_Rate]),"Mean_Childbearing_Age",SUM(Data[Mean_Childbearing_Age]),"Pop_Sex_Ratio_At_Birth",SUM(Data[Pop_Sex_Ratio_At_Birth]),"Tot_Deaths_000s",SUM(Data[Tot_Deaths_000s]),"Male_Deaths_000s",SUM(Data[Male_Deaths_000s]),"Female_Deaths_000s",SUM(Data[Female_Deaths_000s]),"Crude_Death_Rate",SUM(Data[Crude_Death_Rate]),"Life_Exp_At_Birth",SUM(Data[Life_Exp_At_Birth]),"Male_Life_Exp_At_Birth",SUM(Data[Male_Life_Exp_At_Birth]),"Female_Life_Exp_At_Birth",SUM(Data[Female_Life_Exp_At_Birth]),"Life_Exp_At_15Yrs",SUM(Data[Life_Exp_At_15Yrs]),"Male_Life_Exp_At_15Yrs",SUM(Data[Male_Life_Exp_At_15Yrs]),"Female_Life_Exp_At_15Yrs",SUM(Data[Female_Life_Exp_At_15Yrs]),"Life_Exp_At_65Yrs",SUM(Data[Life_Exp_At_65Yrs]),"Male_Life_Exp_At_65Yrs",SUM(Data[Male_Life_Exp_At_65Yrs]),"Female_Life_Exp_At_65Yrs",SUM(Data[Female_Life_Exp_At_65Yrs]),"Life_Exp_At_80Yrs",SUM(Data[Life_Exp_At_80Yrs]),"Male_Life_Exp_At_80Yrs",SUM(Data[Male_Life_Exp_At_80Yrs]),"Female_Life_Exp_At_80Yrs",SUM(Data[Female_Life_Exp_At_80Yrs]),"Infant_Deaths_Under1Yr_000s",SUM(Data[Infant_Deaths_Under1Yr_000s]),"Infant_Mortality_Rate",SUM(Data[Infant_Mortality_Rate]),"Births_Surviving_1Yr",SUM(Data[Births_Surviving_1Yr]),"Deaths_Under_5Yrs_000s",SUM(Data[Deaths_Under_5Yrs_000s]),"Mortality_Under_5Yrs",SUM(Data[Mortality_Under_5Yrs]),"Mortality_Under_40Yrs",SUM(Data[Mortality_Under_40Yrs]),"Male_Mortality_Under_40Yrs",SUM(Data[Male_Mortality_Under_40Yrs]),"Female_Mortality_Under_40Yrs",SUM(Data[Female_Mortality_Under_40Yrs]),"Mortality_Under_60Yrs",SUM(Data[Mortality_Under_60Yrs]),"Male_Mortality_Under_60Yrs",SUM(Data[Male_Mortality_Under_60Yrs]),"Female_Mortality_Under_60Yrs",SUM(Data[Female_Mortality_Under_60Yrs]),"Mortality_Under_15_50Yrs",SUM(Data[Mortality_Under_15_50Yrs]),"Male_Mortality_Under_15_50Yrs",SUM(Data[Male_Mortality_Under_15_50Yrs]),"Female_Mortality_Under_15_50Yrs",SUM(Data[Female_Mortality_Under_15_50Yrs]),"Mortality_Under_15_60Yrs",SUM(Data[Mortality_Under_15_60Yrs]),"Male_Mortality_Under_15_60Yrs",SUM(Data[Male_Mortality_Under_15_60Yrs]),"Female_Mortality_Under_15_60Yrs",SUM(Data[Female_Mortality_Under_15_60Yrs]),"Net_Migrants_000s",SUM(Data[Net_Migrants_000s]),"Net_Migration_Rate",SUM(Data[Net_Migration_Rate]),blank()))

The issue I have is that I dont know how to link the Legend field of my shape map to the slicer . I cant drag

Measure_Selections table into the legends field of the map and when I try to drag the Measure_Selections table with the field to which the measures slicer refers I get the following error "an't determine a relationship between two or more fields shape map"

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Check out the August 2024 Power BI update to learn about new features.

Featured Topics

Top Solution Authors

User | Count |
---|---|

109 | |

79 | |

72 | |

48 | |

39 |

Top Kudoed Authors

User | Count |
---|---|

138 | |

108 | |

69 | |

64 | |

57 |