Displaying multiple-select questions on map legend

[bssdon]

I have data that comes from a multiple-select question that is stored in multiple columns like in the table below

Category of incidence	Category of incidence/electricity off	Category of incidence/Flooded road	Category of incidence/fallen tree	Category of incidence/leaning utility pole	Category of incidence/roof leaking	Category of incidence/partial house collapse	Category of incidence/other	Latitude	Longitude
Electricity off flooded road	1	1	0	0	0	0	0
fallen tree leaning utility pole electricity off	1	0	1	1	0	0	0
Partial house collapse	0	0	0	0	0	1	0
roof leaking electricity off	1	0	0	0	1	0	0
roof leaking flooded road other	0	1	0	0	1	0	1

I currently have the Category of Incidence field as the legend, but that ends up eventually being all possible permutations of all the categories. Like this

Is there any way that I can have a legend of one of each category like

• Electricity off
• Flooded Road
• Fallen tree
• Leaning Utility Pole
• Roof leaking
• Partial House collapse
• Other

And make it so that clicking on one highlights all points on the map that contain that category? is that possible?

[parry2k]

@bssdon you need to unpivot your data to make it work, what would be on the x-axis?

[bssdon]

I have another column for Location for the x-axis. How would I accomplish this? When I try unpivoting the columns it multiplies the number of rows. Apologies as I'm new to this.

[v-xinc-msft]

Hi @bssdon ,

Thank you for your kind reply!

What does all the data you have look like? Could you please describe in more detail the output you expect to get? It's normal for you to mention "When I try unpivoting the columns it multiplies the number of rows. ", We need more information to offer you more help! 

Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

In addition, someone else had a similar issue like yours before, you could check out the link, hopefully it will help you have more ideas.

Solved: Visual-level filters not working - Microsoft Fabric Community

Best regards,

Lucy Chen

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

[bssdon · Accepted solution]

Thank you very much! The link to the discussion with the similar issue helped immensely. Specifically the video that the user linked https://www.youtube.com/watch?v=rI0uHdBwgPY 

To summarize, I had to add the Category of Incidence as a new query (and add an index column), split it into columns, then unpivot all columns except for the index.

[v-xinc-msft]

Hi @bssdon ,

I am happy to learn that you found the cause of the problem and fixed it.

Could you please accept your own reply or my reply as a solution, it will make it easier for other users who may be experiencing the same problem to find a solution.

Thank you for your understanding!

Best regards,

Lucy Chen