Ignore NULL values on chart

[Anonymous]

How do I exclude dimension values that have a NULL measure?  For example, in my bar chart I have employees that do not have data (so their measure value is NULL), but they are still showing up on my chart.  How do I remove them?

When I remove the check from "Include zero values" from the Data Handling menu not only does it remove dimensions with a NULL measure it also removes dimensions with a value of zero.  I want to keep the zero values, but get rid of the NULL values.

[sunny_talwar]

May be make 0 to be 0.00000001 something like this

If(Avg(Measure) = 0, 0.00000001, Sum(Measure))

Now when you will uncheck 'Include zero values', 0s will stay as they are 0.00000001 now rather than 0s

[Anonymous]

I'd rather not have a work around solution.  Especially since there is the ability to export data.  I don't want to show 0.00000001 when the number ahould be 0.

[sunny_talwar]

The other option is to calculated dimension.... I can't think of a 3rd way to do this

[Anonymous · Accepted solution]

I created an Employee dimension in Master Items with the following expression in the Field: Aggr(If($(vMetric)>=0,EmployeeName),EmployeeName).  This formula says if the metric is not null then return the employee.  Essentially, it gives me a dimension with the employees that have the metric.

Then I put this Master Item dimension in the dimension of my chart.  So far, it seems be working (only showing operators who have data in the chart).

[sunny_talwar]

That is a calculated dimension