SOLVED

How to Filter dynamic array with table field-to-dynamic array criteria?

Copper Contributor

Is there a way - with the Filter function or an alternative - to replicate below function but allowing for $G3# to replace $G3?
FILTER(Table2[Date],(Table2[Symbol]=$G3)
Thanks

6 Replies

@ds100 

Since G3# includes all values of "Symbol" a filter on G3# would include all rows - making the filter obsolete.

=TEXTJOIN(", ",,TEXT(UNIQUE(Table2[Date]),"m/d"))

 

best response confirmed by Grahmfs13 (Microsoft)
Solution

@ds100  alternatively i think you want something like this:

=BYROW(G3#,LAMBDA(r,TEXTJOIN(", ",,TEXT(UNIQUE(FILTER(Table2[Date],(Table2[Symbol]=r),"")),"m/d"))))

using BYROW lets you perform the operation on each row of the G3# spill 

@ds100 

Practically the same

=LET(
  u, UNIQUE(Table2[Symbol]),
  IF( {1,0}, u,
      MAP( u, LAMBDA(v, TEXTJOIN(",", 1, TEXT( FILTER(Table2[Date], Table2[Symbol] = v), "m/d" ) ) ) ) ) )

@Sergei Baklan 

Map function came in handy for something else. Thanks again

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@ds100  alternatively i think you want something like this:

=BYROW(G3#,LAMBDA(r,TEXTJOIN(", ",,TEXT(UNIQUE(FILTER(Table2[Date],(Table2[Symbol]=r),"")),"m/d"))))

using BYROW lets you perform the operation on each row of the G3# spill 

View solution in original post