Announcements
cancel
Showing results for
Did you mean:
Creator III

## Nested If & Pick

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)

1 Solution

Accepted Solutions
MVP

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))

2 Replies
Partner - Contributor II

Hi,

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

Sum(

Pick(DimA,

Pick(DimB, 1, 2, 3),

Pick(DimB, 4, 5, 6),

Pick(DimB, 7, 8, 9)

)

)

Regards

MVP

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))

Community Browser