Chart Legend Null Override Name Visual

[arimoldi]

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 

[Anonymous]

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:

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

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"))

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".

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!

[arimoldi]

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

[Ritaf1983]

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 · Accepted solution]

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")

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)

result

pbix is attached

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