Dynamic array -select all- slicers

[PvO123435]

Hello all,

I could really need some help with a formula. See this example.

Basicly I'm trying to make a custom pivot table that has to interact with a couple of slicers. Only problem is that all these slicers also need to have a 'select all' option. I can't figure out how to solve this.

For example:

If I choose customer A, product 1, market all in the slicer; the expected result should be 100 instead of 1200.

Any help would be greatly appreciated!

[#2 · Accepted solution]

@PvO123435 

Perhaps

=LET(
x,IFERROR(UNIQUE(FILTER(Tabel1[Customer],(IF(L8="All",Tabel1[Customer],L8)=Tabel1[Customer])*(IF(M8="All",Tabel1[Product],M8)=Tabel1[Product])*(IF(N8="All",Tabel1[Market],N8)=Tabel1[Market]))),"No match"),
z,IFERROR(UNIQUE(FILTER(Tabel1,(IF(L8="All",Tabel1[Customer],L8)=Tabel1[Customer])*(IF(M8="All",Tabel1[Product],M8)=Tabel1[Product])*(IF(N8="All",Tabel1[Market],N8)=Tabel1[Market]))),"No match"),
yy, TRANSPOSE( MMULT(  TRANSPOSE( INDEX( z, 0, 4) ), --( TRANSPOSE(x)=INDEX(z,0,1) )) ),
CHOOSE({1,2},x,yy)
 )

[#3]

Wow this is great! Trying to figure out what you exactly did, but the solution is perfect. Thanks a lot!

[#4]

@PvO123435 , you are welcome