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)