Hi,
I have a clusterd column chart that shows various fields from my data. However, I want to show only the fields that have values in it. Ideally, I'd like the legend to only show what's in the graph. See my example:
background:
Each row of data is from a facility providing specific food. Each food item is a seperate column. Values will show null if they facility did not provide that food. See below:
In a perfect world i would just have the visual show only values greater than 0. Or, if there's a way to filter the legends for only values that are in the graph rather than a monster list of 12 when there's only 4 columns returning data. How do I best achieve this?
Thank you!
@Anonymous
I used your sample data and created what you asked for.
Here are the steps I did in Query editor.
1. Before automatic type conversion, select the columns other than District,ward,school,month(select green_vegetables till salt - 11 columns ; Make sure the selected columns are in alphabetic datatype)
2. In the ribbon, click Transform->unpivot dropdown->selected columns. After this step, the data will look like below.
3. Change the value column to decimal number format.
4. Close & Apply
Now create a new table as below. (I didn't rename the attribute column in previous step)
Define a relationship between the tables based on Attribute.
Create the visual as below.
If it helps, mark it as a solution
Kudos are nice too
Hi @VasTg ,
thank you for the response.
I'm not sure I understand since my legend is not a seperate table, but instead each value in the chart is a seperate column. I'm not sure I can create a new table for my legend and link them.
Here's a sample of what it looks like with the example of the data driving the visual:
This is the underlying data with each column representing each value in the visual above. As you can see there are 10 fields in the legend but only 4 have values when you sum for each respective column. I'd like to have the legend correspond with the graph. Potentially new records could be any of the 10 potential options.
| location District | location Ward | location School_id | Month | green_vegetables | fruits | other | beans | maize | sorghum | rice | OFSP | millet | banana | salt |
| Bunda- Urban | Kabasa | 1094 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buhemba | 1149 | January | null | null | null | null | null | null | null | null | null | null | null |
| Musoma | Bukumi | 1159 | January | 20 | null | null | null | null | null | null | null | null | null | null |
| Bunda DC | Butimba | 1033 | January | null | 20 | null | null | null | null | null | null | null | null | null |
| Butiama | Mirwa | 1133 | January | 150.4 | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buhemba | 1148 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buhemba | 1151 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buhemba | 1152 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buhemba | 1150 | January | null | null | null | 85 | null | null | null | null | null | null | null |
| Butiama | Mirwa | 1134 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Mirwa | 1132 | January | 330 | null | null | null | null | null | null | null | null | null | null |
| Butiama | Masaba | 1143 | January | 50 | null | null | null | null | null | null | 121 | null | null | null |
| Butiama | Masaba | 1145 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Masaba | 1146 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Masaba | 1147 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buruma | 1128 | January | null | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buruma | 1130 | January | 20 | null | null | null | null | null | null | null | null | null | null |
| Butiama | Buruma | 1129 | January | null | null | null | null | null | null | null | null | null | null | null |
thank you and hope my explanation makes sense. Thanks for taking the time to help.
@Anonymous
I used your sample data and created what you asked for.
Here are the steps I did in Query editor.
1. Before automatic type conversion, select the columns other than District,ward,school,month(select green_vegetables till salt - 11 columns ; Make sure the selected columns are in alphabetic datatype)
2. In the ribbon, click Transform->unpivot dropdown->selected columns. After this step, the data will look like below.
3. Change the value column to decimal number format.
4. Close & Apply
Now create a new table as below. (I didn't rename the attribute column in previous step)
Define a relationship between the tables based on Attribute.
Create the visual as below.
If it helps, mark it as a solution
Kudos are nice too
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
