Conditional legend based on slicer for Azure Map with individual lat-long points

[ForumsDB]

Hi all,

I was inspired by seeing some reports that used dynamic legends for charts through the use of setting up inactive relationships and a field-category reference table. What I would love to be able to do is use the same principle for a report I have that includes a series of azure maps with lat+long pairs of locations - where instead of having to switch pages to different legend sets (based on columns/groups in the original data), to be able to switch between them on the same page. 

I have managed to get it to change the legend items themselves by the selection, which is ultimately using the slicer to filter down the options on the legends query table, but I cannot get to the final step, because as soon as I try to introduce the Latitude and Longitudes, they need the relationships to be operational, but I cannot figure out the right Calculate function that will allow me to userelationship to activate the links but also be using individual map dots. 

I cannot seem to find anything online that might help, but the videos that inspired me were: Parker Stevens "Power BI Dynamic Axes and Legends" and Goodly's Create Interesting Maps in Power BI (which uses a slicer to pick the "n" in TopN for conditional highlighting). 

Any ideas? (and thank you!)

[Ahmed-Elfeel · Accepted solution]

Hi @ForumsDB,

The issue here with you is that when you introduce latitude/longitude coordinates, Power BI needs active relationships to properly filter and display the map points but you need inactive relationships for the dynamic legend switching

So Here is Some solutions you could try:

• Create measures that selectively activate relationships with USERELATIONSHIP (The most effective solution)

Dynamic Map Measure = 
CALCULATE(
    [Your Map Value],
    USERELATIONSHIP(LegendCategories[Category], PBILINK[Role]),
    USERELATIONSHIP('YourDataTable'[Key], PBILINK[LinkKey])
)

•  If the previous approach not works for you, you can use DAX measure to dynamically determine legend categories:

Dynamic Category = 
SWITCH(
    TRUE(),
    ISFILTERED(LegendCategories[Role (HighLevel)]), SELECTEDVALUE(PBILINK[Role (HighLevel)]),
    ISFILTERED(LegendCategories[Role - Level)]), SELECTEDVALUE(PBILINK[Role - Level)]),
    SELECTEDVALUE(PBILINK[Role])
)

Bonus approach (without measures) 

• Create a dedicated bridge table that connects your legend categories to your coordinate data
  • Build a unified dimension table that contains all possible legend categories

  • Create active relationships from this bridge to both your legend data and coordinate data

  • Finally use this bridge (you created) for filtering instead of relying on inactive relationships
• This solution also works if you dont need to use DAX (this often provides the most stable solution)

Tell me if it works I am waiting ☺️❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[v-pnaroju-msft]

Thankyou, @ Ahmed-Elfeel for your response.

Hi ForumsDB,

Thank you for the update and we are glad to hear that the unpivot (or bridge) approach is working for you.

Based on my understanding, Azure Maps requires actual model columns (not measures) for both the Legend and Latitude/Longitude fields. When a Legend field is a measure, the visual displays: “This field can’t be used here because a non-measure field is required.” This behaviour is expected, because Azure Maps needs fixed categorical fields and physical numeric coordinate columns to render points correctly.

Measures cannot be used in the Legend bucket. Azure Maps accepts only a column for Legends, which is why SWITCH based legend logic cannot be applied there. Your current unpivoted CategoryType/CategoryValue structure correctly provides the visual with a single categorical column, which is why it works.

If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[Ahmed-Elfeel · Accepted solution]

Hi @ForumsDB,

The issue here with you is that when you introduce latitude/longitude coordinates, Power BI needs active relationships to properly filter and display the map points but you need inactive relationships for the dynamic legend switching

So Here is Some solutions you could try:

• Create measures that selectively activate relationships with USERELATIONSHIP (The most effective solution)

Dynamic Map Measure = 
CALCULATE(
    [Your Map Value],
    USERELATIONSHIP(LegendCategories[Category], PBILINK[Role]),
    USERELATIONSHIP('YourDataTable'[Key], PBILINK[LinkKey])
)

•  If the previous approach not works for you, you can use DAX measure to dynamically determine legend categories:

Dynamic Category = 
SWITCH(
    TRUE(),
    ISFILTERED(LegendCategories[Role (HighLevel)]), SELECTEDVALUE(PBILINK[Role (HighLevel)]),
    ISFILTERED(LegendCategories[Role - Level)]), SELECTEDVALUE(PBILINK[Role - Level)]),
    SELECTEDVALUE(PBILINK[Role])
)

Bonus approach (without measures) 

• Create a dedicated bridge table that connects your legend categories to your coordinate data
  • Build a unified dimension table that contains all possible legend categories

  • Create active relationships from this bridge to both your legend data and coordinate data

  • Finally use this bridge (you created) for filtering instead of relying on inactive relationships
• This solution also works if you dont need to use DAX (this often provides the most stable solution)

Tell me if it works I am waiting ☺️❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[ForumsDB · Accepted solution]

Thank you so much for your suggestions!

I have some questions:

The Dynamic category option will not apply to the map legend, but not sure I am getting that one to work successfully, but this might be me missing the point here. 

With the first suggestion, the dynamic map measures - what am I to be using for those [map value] points? I had tried to make some switch measures for latitudes or longitudes in different selections but it kept throwing errors. 

That being said, the third option worked for what I needed it to, or at least how I interpreted it! 

I took the base data [pbilink] and duplicated it, removed a bunch of columns and then unpivoted the fields that I used for the categories. Then linked them up index:index and flowed the "options" from the table that I use on the slicer into the legend categories table, which is linked to the values in that new table. 
I might need to go back and add a column to the original to get the third one I want as that was made by grouping, but this has allowed me to do what I needed, even if perhaps not the most elegantly(!) Thank you!