Reduce indirect and Vstack

Question

I hope this makes sense using reduce ;

REDUCE( INDIRECT( TAKE( Table13[Store],1)), DROP(Table13[Store],1), LAMBDA(s,c, VSTACK(s, INDIRECT( c ) )))

This works, but if you chang slightly so indirect is used only in the vstack;

REDUCE( TAKE( Table13[Store],1), DROP(Table13[Store],1), LAMBDA(s , c, VSTACK( INDIRECT( s ), INDIRECT( c ) )))

so indirect is used in vstack for both it fails, unless I'm making some stupid typo / error any ideas why this should happen ?

Answer 1

Hello RichardDrake1505,

* I am a Microsoft user like you, providing solutions to community members; I am NOT a Microsoft employee.

The reason your formula breaks when using INDIRECT inside VSTACK twice is because LAMBDA captures the value of s and c at the time the function is defined, not when it's called within REDUCE.

Here's how you can fix it:

Option 1: Use INDEX Instead of INDIRECT Instead of INDIRECT, use INDEX to reference the elements within Table13[Store]. This approach relies on the actual positions of s and c within the table:

REDUCE(TAKE(Table13[Store],1), DROP(Table13[Store],1), LAMBDA(s,c, VSTACK(INDEX(Table13[Store], s), INDEX(Table13[Store], c))))

Option 2: Use a Nested LAMBDA You can use a nested LAMBDA function within your main LAMBDA to access the current values of s and c during each iteration of REDUCE:

REDUCE(TAKE(Table13[Store],1), DROP(Table13[Store],1), LAMBDA(s,c, VSTACK( LAMBDA(x, INDIRECT(x)) (s),
LAMBDA(x, INDIRECT(x)) (c) ) ) )

Hope this helps.

• Stephen N.

Answer 2

Yes it does work.

if you have any queries please direct them to victor;

(29) Reduce function in excel to stack tables with VSTACK - YouTube

Richard

Answer 3

Thanks, very thorough, so it is similar to when you declare a variable for a DAX measure?

RD