Nested If & Pick

[KHSDM]

Hi all,

I'm trying to build a pivot table where the column and row will be expression. I use the Pick method to create the table. But I have a problem when I have 2 dimensions. When I collapse the first dimension, the row will disappear (due to row value = null). How can I add an expression for the first dimension? It is do-able if I use the Nested If, but the expression will be very messy. I hope there is a way with the Pick Method.

Please refer to the attached QVW for the example between IF vs Pick.

*This is a simplified version of the actual pivot.

*Unchecking the "suppress zero" value is not an option. (I need to suppress null value in DimB)

*This table have multiple expression columns. (not included in this example)

[andre_mueller]

Hi,

if I'm not mistaken, then eclosing the pick functions with an aggregation like Sum() should do the trick.

For your example:

Sum(

Pick(DimA,

Pick(DimB, 1, 2, 3),

Pick(DimB, 4, 5, 6),

Pick(DimB, 7, 8, 9)

)

)

Regards

[sunny_talwar · Accepted solution]

May be use this with pick

=Sum(Aggr(Pick(DimA,

Pick(DimB, 1, 2, 3),

Pick(DimB, 4, 5, 6),

Pick(DimB, 7, 8, 9)

), DimA, DimB))