How to apply legends in shape Map visual using calculated column or dax

[spathak04]

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

[v-juanli-msft · Accepted solution]

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

[spathak04]

 @v-juanli-msft Hello,

Did you find any solution for the sort order on legends?

Thanks

[stonegg]

Hi, I have similar question on this issue. Can you tell me where to find the solution for sort order on those dynamic legend that was determined by the filters?

[v-juanli-msft]

Hi @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

[spathak04]

@v-juanli-msft 
Yes his solution was correct when I was using different bookmark for each attribute. But now I am using a dropdown for my attributes like (GDp , population and etc). my table structure is like this: -


I am not sure how to write custom sort order logic in query editor. As before I created different sort order columns each for GDp, Population etc. Now I have every attribute in one column. 

I tried to add custom sort order column but every attribute has different categories/ values.  Bu this will not work.


Can you please show me how it should be done. Thanks

[spathak04]

@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

[v-juanli-msft]

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

[spathak04]

@v-juanli-msft,
Yes correct. My table structure is same as you mentioned.