Forum Discussion
How to apply legends in shape Map visual using calculated column or dax
Hello,
Scenario is, in my data set I have 20 columns which are attributes like GDP, Population etc. Then I created a dimension table having all these attributes in it and put them in a slicer. I have a shape map whose value gets change based on slicer selection.
My requirement is, every attribute has different range like GDP range : - 15-20, 20-30, 50-80 and Population : - 550-1200, 1200-3000(something like this). I want to show this range in a legends and want to change legends dynamically based on slicer selection.
This is currently look like this
I want legends like this based on attribute selection
To get the legends I have a calculated column but I can only specify one attribute in it, for example
GDPCategory = IF(Table_Name[GDP] >= 25, "25% or more", if(Table_Name[GDP] >10,
"10% - 25%", if(Table_Name[GDP] >=3, "3% - 10%", IF(Table_Name[GDP] >0, "0% - 3%",
if(Table_Name[GDP] <0, "less than 0%", "no data")))))
I want all these category should be dynamic based on my slicer selection.
Thanks
Hi spathak04
In Queries Editor
Select “GDP”,”Population”.. columns, then select “Unpivot columns”
Close &&Apply, Go to Data View, create a calculated column
category = VAR GDPCategory = IF ( Sheet3[Value] >= 25, "25% or more", IF ( Sheet3[Value] >= 10, "10% - 25%", IF ( Sheet3[Value] >= 3, "3% - 10%", IF ( Sheet3[Value] > 0, "0% - 3%", IF ( Sheet3[Value] < 0, "less than 0%", "no data" ) ) ) ) ) VAR PopulationCategory = IF ( Sheet3[Value] >= 3000, "3000 or more", IF ( Sheet3[Value] >= 1200, "1200 - 3000", IF ( Sheet3[Value] >= -550, "-550 - 1200", "no data" ) ) ) RETURN SWITCH ( Sheet3[Attribute], "GDP", GDPCategory, "Population", PopulationCategory )Best Regards
Maggie
8 Replies
Hi spathak04
In Queries Editor
Select “GDP”,”Population”.. columns, then select “Unpivot columns”
Close &&Apply, Go to Data View, create a calculated column
category = VAR GDPCategory = IF ( Sheet3[Value] >= 25, "25% or more", IF ( Sheet3[Value] >= 10, "10% - 25%", IF ( Sheet3[Value] >= 3, "3% - 10%", IF ( Sheet3[Value] > 0, "0% - 3%", IF ( Sheet3[Value] < 0, "less than 0%", "no data" ) ) ) ) ) VAR PopulationCategory = IF ( Sheet3[Value] >= 3000, "3000 or more", IF ( Sheet3[Value] >= 1200, "1200 - 3000", IF ( Sheet3[Value] >= -550, "-550 - 1200", "no data" ) ) ) RETURN SWITCH ( Sheet3[Attribute], "GDP", GDPCategory, "Population", PopulationCategory )Best Regards
Maggie
v-juanli-msft Hello,
Did you find any solution for the sort order on legends?
ThanksHi spathak04
I see this link which you post the "sort legend" problem.
TomMartens has point out the right direction to do this.
For your problem when inplement this method, i would like to explain here.
From your information, you created a calculated column using the column which need to be sorted, right?
In this way, it doesn't work.
Could you open the "Queries Editor", if so, go to "Queries Editor" , create a custom column to define your column's order.
Then close&&apply, in the Report view, "sort your column by this custom column", it should work.
If you have any problem, please let me know.
Best Regards
Maggie
v-juanli-msft,
It's a great help, I was really looking for this kind of solution. Thank you so much.
Can you please tell me one more thing, how can I sort the legends also.
For example 6-3 should come first, then 3-0, then 0 then no data.
How can I sort legends?
THANKS IN ADVANCE
Hi spathak04
Does your table structure like this?
location year GDP Population … a 2016 27 100 a 2017 15 1500 a 2018 6 3500 b 2016 1 2000 b 2017 -7 1000 b 2018 24 4000 Best Regards
Maggie
v-juanli-msft,
Yes correct. My table structure is same as you mentioned.
