Forum Discussion
Need Help: Multiple/Dynamic Legend in Bar chart for survey data
Hi,
I am looking for some inputs on how to create a visual (attached) with the survey data. I want multiple/dynamic legend in the bar chart with I can switch with a chiclet slicer. I am not very keen to use bookmarks to toggle between the views. I have skmieed through most of the resources on the web, but could not find much help. I want to see survey responses by age, gender, nation, customer trypes on the same page on the same bar chart - as i make selections on the chiclet slicer.
Sample (made up) data below...
Will be very grateful if anyone can help
| Ref ID | Age | Gender | Nation | Customer Type | Response to Q1 | Sales |
| 1 | 18-30 | F | UK | A | Yes | 10 |
| 2 | 31-45 | M | US | A | No | 30 |
| 3 | 46-60 | F | Spain | B | Don’t know | 40 |
| 4 | 18-30 | M | France | B | Yes | 50 |
| 5 | 31-45 | F | India | C | Yes | 60 |
| 6 | 46-60 | M | China | D | No | 70 |
| 7 | 18-30 | F | US | B | Don’t know | 80 |
| 8 | 31-45 | M | Spain | C | Yes | 30 |
| 9 | 46-60 | F | France | D | Yes | 40 |
| 10 | 18-30 | M | UK | A | No | 50 |
| 11 | 31-45 | F | US | B | Don’t know | 120 |
| 12 | 46-60 | M | Spain | C | Yes | 130 |
gt2904 - There are three general approaches to this. One can be found here and involves a very specific way of modeling your data. https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titles-chart-types/m-p/1027881#M444
Another is to use bookmarks, which you don't want.
The third would be to build the switching logic into your measure(s) and use the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
So, in your case, you would have a table for your legend like:
Slicer Group Age 0-18 Age 19-blah Age blah-blah Country UK Country US You could build this disconnected table very easily with DAX using DISTINCT and such. Anyway, your slicer would slice on the slicer column and your legend would be the Group column. But, since it is a disconnected table, you would need to write your measures such that they formed the relationship between your fact table and this disconnected table.
2 Replies
gt2904 - There are three general approaches to this. One can be found here and involves a very specific way of modeling your data. https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titles-chart-types/m-p/1027881#M444
Another is to use bookmarks, which you don't want.
The third would be to build the switching logic into your measure(s) and use the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
So, in your case, you would have a table for your legend like:
Slicer Group Age 0-18 Age 19-blah Age blah-blah Country UK Country US You could build this disconnected table very easily with DAX using DISTINCT and such. Anyway, your slicer would slice on the slicer column and your legend would be the Group column. But, since it is a disconnected table, you would need to write your measures such that they formed the relationship between your fact table and this disconnected table.
gt2904 , refer if this can help
https://prathy.com/2018/12/dynamic-legend-in-power-bi-visual-linechart/
