Suppress dimension if measure returns null but NOT 0

[lwestmaas]

Hi all,

We have business units that have closed, so their values for the current year are obviously 0 since they do not exist. There are other units that DO exist in the current year but have actual 0 values.

Values for closed business units return as null (they are percentages being divided by 0), but values for true 0s return as 0.

I know I can use the "Include Zero Values" check box to remove both. How can I remove ONLY dimensions that return null values and not dimensions that return true zero?

The only solution I've come up with is load a separate dimension that excludes closed business units; this is not ideal because I want the closed units to display if a user wants to look at data from previous years.

Thank you!

Lucas

[Digvijay_Singh]

Did u try unchecking 'Include null values' in the dimension properties?

Or may be {<DimensionField={"*"}>} in the measure expression

[lwestmaas]

That doesn't work, unfortunately. The issue is that the dimension is not null but the output of the measure is.

[Digvijay_Singh]

You may try this may be in the measure-
If len(Measure)>0, Measure)

[vikasmahajan]

Hi,

Have you try with un-tick include zero values option ?

Vikas

[lucpechali]

Hi,

Maybe it will be easier to flag your data in the script when its closed business units and use the flag value to show (or not) your dimension in the chart.

That's what i always use.

[lwestmaas]

Can you be more specific? Do you mean the flag would be contained as a set analysis in the measure? Unless I'm misunderstanding that won't resolve the issue; the measures are already resolving to null values for the closed units.

[lucpechali]

hi again,

No, prepare the data in the load script.

Ex :

Load *, if([Business Unit]= 'X' and DATE_Closed>=today() , 1, 0) as Flag_Open_Close

Resident MyTable;

Then, you could use this flag as a shown condition in your dimension 

Ex: Flag_Open_Close=1

[lwestmaas]

What do you mean by "shown dimension"? To be clear, this issue arises when I am breaking data out by current region - e.g. "Region 1", "Region 2", and "Closed". Thus I am already using a dimension and in some cases due to chart type/options cannot add a second dimension. This is not an issue with data from closed units affecting whole-system calculations.

[bbergstrom]

Would something like this work in the dimension field:

IF(Not IsNull(Measure),Dimension value)

Then make sure Include Null Values is unchecked.