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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

 

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

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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