Dynamic fields on Aggr

[danielact]

Is there a way to change the fields used on an Aggr function? I'd like to use it in a the dimension of a Pivot table and I'm having some trouble.

Let's say I have 4 fields I'm using as dimensions - Field1, Field2, etc.

So I'm using (just to simplify) aggr(Number, Field1, Field2, Field3, Field4) as a part of a dimension.

But now, let's say I want to collapse one of the dimensions in the Pivot - let's say Dimension 3, so now I'll only be showing 3 dimensions, not 4. Is there a way to change the aggr function to only aggregate by what's open?

[CELAMBARASAN]

Hi,

     Are you using aggr in expression?

Celambarasan

[CELAMBARASAN]

Hi,

Try with this type of approach

     If(IsNull(Only(Field2)), Aggr(Number,Field1),

     If(IsNull(Only(Field3))), Aggr(Number, Field1, Field2),

     If(IsNull(Only(Field4)), Aggr(Number,Field1, Field2, Field3), Aggr(Number, Field1, Field2, Field3, Field4))))

Hope it helps

Celambarasan

[#4]

Have you tried using the Dimensionality() function?

This function returns the number of expanded dimensions in the pivot table.

You can use it to define If..else... statement in the Pivot table expression.

[danielact]

The dimensionality function looks like it might help, if there's a way to also get the field name for each level. Any idea if there's a function to return that value?

[danielact]

It turns out that the dimensionality function won't work for me, as I need to use this is the dimension, not the expression.

Any other ideas on how to do this?