Hide empty (null) values in PivotTable

[Jan_N]

Hi,

how can I hide empty fields with null values and avoid displaying the indicator (+) in this cases:

In case of "Test1" only "(+)" should be displayed for "Name".

In case of "Test2" only "(+)" should be displayed for "Name" and "Level".

If I uncheck "Include null values" for the dimensions, I only see the "Test3" case.

Data load:

SET NullInterpret='';

Test:
Load * Inline [
Name, Level, Level2, Level3
Test1, x,,
Test2, x,y,
Test3, x,y,z
];

Thank you in advance.

[fosuzuki]

Hi,

try this expression and also disable "Include zero values".

* be careful because this expression may impact performance...

Hope this helps

[Jan_N]

Hi Fosuzuki,

thank you for the quick response. I can't see the expression. Is there something missing?

[fosuzuki · Accepted solution]

Sorry, guess I forgot to CTRL+V.

=if(not((Isnull(Level3) and dimensionality()=4) or (Isnull(Level2) and dimensionality()=3)), 1)

[Jan_N]

Thank you very much. Your expression works well.

I also tried this with "real data" and up to 7 dimensions. I don't see any performance issues at the moment.

The only thing I don't understand is why the "(+)" is shown in Test1 -> x. There's nothing to ungroup?

[MaWi]

Hi,

i run in the same issue as @Jan_N . I used his example and tried your solution @fosuzuki 

But unfortunately i doesn't work for me. Is there anything else what i should do in the pivot table?

Include Zero Values Activated

Include Zero Values Deactivated

[Jan_N]

Hi,

the option must be activated for the dimensions.

There is another setting under Add-ons -> Data handling -> Include zero values

If I deactivate this it works for me.

[MaWi]

Ah thanks a lot ... I deactivated Include Zero Values under dimensions always ... Deactivating Add-Ons > Data handling > Include zero Values works perfect.

Thanks a lot for the fast reply!