Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
arimoldi
Resolver I
Resolver I

Chart Legend Null Override Name Visual

Hi,

 

I have a chart with a legend based on numeric values (from -4 to +4) that contains also NULL values, but in the legend in the chart I would like to change the nullvalue in the legend with a string like "Not Available".

 

I tried to create a domain table with custom label but when I connect it to the model I get a many to many *:* relationship instead of a one to many 1:* relationship due to the null values I suppose.

 

Do you have any suggestion on how to do it?

 

Thanks,

Andrea 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @arimoldi 
You can add a legend column to your table:

Legend = SWITCH([category],
-1,"-1",
-2,"-2",
-3,"-3",
-4,"-4",
1,"1",
2,"2",
3,"3",
4,"4",
"Not avalible")
Ritaf1983_0-1706758495390.png

and sort the legend column to have this "Not Available". et the end of list

Sort legend =
SWITCH([category],
-1,0,
-2,1,
-3,2,
-4,3,
1,4,
2,5,
3,6,
4,7,
8)
 
Ritaf1983_1-1706758569191.pngRitaf1983_2-1706758603899.png

result

Ritaf1983_3-1706758650020.png

pbix is attached

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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

4 REPLIES 4
v-huijiey-msft
Community Support
Community Support

Hi @arimoldi ,

 

Hope all is going well.

 

To reproduce your problem, I created a data table based on numeric values (from -4 to +4) that also contains NULL values:

vhuijieymsft_0-1706767476420.png

 

This is the visual representation of the chart under the original data.

vhuijieymsft_1-1706767485431.png

 

In order to achieve your needs and use "Not Available" to replace null values, I created a calculated column with the following syntax:

 

 

Column = IF(ISBLANK([Num]),"Not Available",FORMAT([Num],"General Number"))

 

vhuijieymsft_2-1706767514074.png

 

After creating the calculated column, replace the field used to display the visual object with the created calculated column. The null value at this time will be replaced by "Not Available".

vhuijieymsft_3-1706767526025.png

 

PBIX attached.

 

If you have any other questions or need my help, please feel free to contact me.

 

Warm Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Hi,

 

thanks for the solution.

 

I tried to implement it, the new issue I get is that the legend cannot be ordered as a number (-4, -3, -2, -1, ...) but is orderered as a string (-4, +4, -3, +3, ...).

 

Is it possible to order the values as number?

 

Thanks,

Andrea

 

Hi @arimoldi 
Take a look at my suggestion in the previous response, it includes a solution for a sort issue too.

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Ritaf1983
Super User
Super User

Hi @arimoldi 
You can add a legend column to your table:

Legend = SWITCH([category],
-1,"-1",
-2,"-2",
-3,"-3",
-4,"-4",
1,"1",
2,"2",
3,"3",
4,"4",
"Not avalible")
Ritaf1983_0-1706758495390.png

and sort the legend column to have this "Not Available". et the end of list

Sort legend =
SWITCH([category],
-1,0,
-2,1,
-3,2,
-4,3,
1,4,
2,5,
3,6,
4,7,
8)
 
Ritaf1983_1-1706758569191.pngRitaf1983_2-1706758603899.png

result

Ritaf1983_3-1706758650020.png

pbix is attached

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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.