Legend with dynamic data in Shape MAP

[tjalleph]

I've been trying to get a categorical legend to work in Shape Map, but I can only get it to work partially. 

I want to show the minimal travel time from an origin (postal code) to selected destinations. Below are two graphs of the same data.
The left map shows the regular minimal travel time through a MIN() measure and a gradient.
For the right map, I want to show categorical colors, based on minimal travel time. I've used a method from this topic, but can't get it to work properly.

As you can see, there are certain shapes that do not show up in the righthanded map.

Folllowing the linked topic, i've created two measures and legend table, with an extra measure

I've created two regular measures: 

1. _traveltime = min('TimeTravel (arrays)'[travel_time (min) ])
2. _traveltime_category =

                if([_traveltime]<=15 , "<15 min",

                    if ([_traveltime]>15 &&[_traveltime]<=30,"<30 min",

                        if([_traveltime]>30 &&[_traveltime]<=60,"< 1h",

                            if([_traveltime]>60 &&[_traveltime]<=90,"1-1.5h",

                                ">1.5h"

                            )

                        )

                    )  

                )

and in a created table Legend:

  

Type	Index
0 min	1
<15 min	2
<30 min	3
< 1h	4
1-1.5h	5
>1.5h	6

_Legend =

var _t=ADDCOLUMNS((SELECTCOLUMNS('TimeTravel (arrays)',"PC4",'TimeTravel (arrays)'[origin (PC4)])) ,"L", _traveltime_category])

return

CALCULATE([_traveltime],FILTER(_t,[L]=MAX('Legend'[Type])))

In the righthanded map, I use the Type-column as a legend, while using _legend as color saturation.

Where do I go wrong in this calculation?

[kushanNa]

Hi @tjalleph 

do you have a special requirement to create the _traveltime_category as a measure ? 

If not have you tried to creating it as a calculated column in TimeTravel table and use it as legend ? 

[tjalleph]

No, I don't need to use this measure. 

The issue with a calculated column is that I want to be able to dynamically filter the map (for example, selecting certain destinations). I've use a calculated column, I'm not able to get the minimal travel time dynamically. 
I've tried summarizing into a new table, but that gives the same issue: i'm not able to get this to work dynamically with slicers/filters.

[kushanNa]

@tjalleph Filter the locations in your main table that are coming up as blank and check if there are any blank values, errors, spaces or unusual formats ?

[tjalleph]

@kushanNa No, they're not empty. 

See the two figures side-by-side. Both are unfiltered, meaning that all shapes have (multiple) datapoints of travel time. I can't find a pattern in this behavior neither: shapes stay empty that have - in the table - all datapoints of the same "traveltime_category" [e.g. "<15min"], or when there are different traveltime_categories for an origin. 

see also example below of the filtered dataset. 

[kushanNa]

@tjalleph it seems like shapemap has a datapoint limit 1500 https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-data-points maybe this can be a reason 

[tjalleph]

@kushanNa Hmm, this could play a role. However, how would that explain the difference between the left- and right-handed map? 

In the underlying table, there are ca 12k records, with only 1k distinct origins. 

[kushanNa]

That I'm not entirely sure, actually. What I would suggest is that you start with a fresh report to make sure there are no hidden filters. Then, try applying some filters on the Power Query side and create a small table including  9,883 location . If you can see the 9,883 location records on both sides, we can assume this might be a data point limit issue. However, if you still cannot see the 9,883 records even after creating a very small table, then it could be a different issue

[tjalleph · Accepted solution]

@kushanNa 
Okay, it seems that i'd created my own problem. 
The original thing, as described in this topic and with above measures, does do the trick.

For some reason, the issue had experienced was due to the fact that I'd checked Show items with no data for the origin. (I had that checked from an earlier version where I'd tried to give a color to the "empty" shapes)

I do not understand why, but unchecking this setting does the trick and shows me what i need!

Thanks for your help!

[FarhanJeelani]

Hi @tjalleph ,

you’re filtering on the wrong thing in Legend.
MAX('Legend'[Type]) returns the lexical maximum of the text values (e.g. ">1.5h"), not the category you are currently evaluating for each origin. That means most origins are filtered away and you end up with blanks on the Shape Map.

Do not filter by MAX('Legend'[Type]). Instead map each origin to its category, and then map that category to the legend index (a numeric value) that drives the color.

Add a small lookup that converts the category text (_traveltime_category) into the numeric Index from your Legend table, and use that as the color saturation measure.

Sample fix (keep your existing measures for travel time and category, then add a color index measure):

Your existing measures (unchanged) _traveltime = MIN('TimeTravel (arrays)'[travel_time (min) ]) _traveltime_category = if([_traveltime] <= 15, "<15 min", if([_traveltime] > 15 && [_traveltime] <= 30, "<30 min", if([_traveltime] > 30 && [_traveltime] <= 60, "< 1h", if([_traveltime] > 60 && [_traveltime] <= 90, "1-1.5h", ">1.5h"))))

New color index measure (maps category to the legend index) _CategoryIndex = COALESCE( LOOKUPVALUE('Legend'[Index], 'Legend'[Type], [_traveltime_category]), 0 )

Use

Legend: Type (from the Legend table)

Color saturation: CategoryIndex

Please mark this post as solution if it helps you. Appreciate Kudos.

[tjalleph]

Thanks!Okay, it's getting closer, i think. 

The _CategoryIndex seems to work, see righthanded figure. However, the moment I add Type as a legend, I end up with the lefthanded figure.

Any additional ideas, @FarhanJeelani ?