Unable to add measure legend in SHAPE MAP that has color saturation based on a certain measure

[Mughees]

Hi,

I am stuck in adding a legend for my shape map. The color saturation of shape map is based on a measured field. That field changes based on mutiple different variables when they are filtered i.e. year, month, area etc. Hence, I cannot add a legend for it based on a a column. 
However, POWER BI do not accept any measure as a legend. Below is the measure and legend for reference.

Measure (Stool_adeq. = COUNTROWS(filter(Sheet1,[ADEQ]="ADEQ"))/((COUNTROWS(filter(Sheet1,[ADEQ]="ADEQ")))+COUNTROWS(filter(Sheet1,[ADEQ]="Inadeq"))) )

Legend = IF([Stool_adeq.]<.7,"< 70%", IF([Stool_adeq.]>=.8<.9,"80-89%",IF([Stool_adeq.]>=.7<.8,"70-79%", IF([Stool_adeq.]>=.9<=1, ">90%", "N/A"))) )

Below are the link of file for reference. both are same 
https://gofile.io/d/Lwa7s0
PBIX file 
If you can edit this file by adding the given legend measure, that would be great.

Thanks and Regards

[v-eqin-msft · Accepted solution]

Hi @Mughees ,

As I mentioned above, please add a new table for legend, then use the following formula to create a measure:

Measure =
VAR _t =
    ADDCOLUMNS (
        DISTINCT ( SELECTCOLUMNS ( 'Sheet1', "dis", 'Sheet1'[DISTRICT] ) ),
        "L", 'Sheet1'[Legend]
    )
RETURN
    CALCULATE ( [Stool_adeq.], FILTER ( _t, [L] = MAX ( 'Legend'[Type] ) ) )

Now you could apply Legend[Type] to Legend field , the final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[weifan]

Hi Eyelyn, 

I have followed your suggestion. However my map only shows the 'Measure' which matched to the highest ranking (i.e >90%). Could you please suggest any reason for this? 

Many thanks, 

Wei 

[ShaneRyder]

Yes I had followed same instructions and came up with same issue, with map only showing the 'Measure' with the highest ranking, I believe it's due to the MAX aggregation within the filter

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

Trying a few different methods now to get around it, I've downloaded @v-eqin-msft 's sample pbix and can't quite see where the difference is which is causing the issue 

[Mughees]

This is super awsome. Many thanks

[v-eqin-msft]

Hi @Mughees ,

I have built a data sample since I could not download the pbix file you provided.

If you want to set "<70%", "70-79%","80-89%",">90%"... as legends , I suggest you create a new table for it:

Then use the following formula to create a measure:

Measure = 
var _t=ADDCOLUMNS(DISTINCT(SELECTCOLUMNS('Sheet1',"ID",'Sheet1'[ID])) ,"L",'Sheet1'[Legend])
return COUNTX(FILTER(_t,[L]=MAX('LegendTable'[Type])),[ID])

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mughees]

Thank you Eyelyn9 for your detailed response. However, the issue I am facing is that I am unable to add measure in the legend in Shape map file. I am sharing the google drive link of the file for reference. Will really appreciate if you can help in resolving the problem for this visual.

PBIX file with shape map