I have a Spill Array. How can I take only top 1 for each product. Basically I want to filter to only values highlighted in yellow.
Formula for Spill Array
=GROUPBY(HSTACK(SL_2025[Product],SL_2025[Description],SL_2025[Root Cause]),SL_2025[Total Adjusted Shortage],SUM,0,0,-4,COUNTIF(Filters!C2#,SL_2025[Sales Office])*COUNTIF(Filters!G2#,SL_2025[Fiscal Week]))
Spill Array: https://i.sstatic.net/ox9ZcJA4.png
I tried to use FILTER function in BYROW but I get an error.
=BYROW(UNIQUE(CHOOSECOLS(H66#,1)), LAMBDA(x, TAKE(FILTER(H66#,CHOOSECOLS(H66#,1)=x,0),1)))
New contributor
Dainius is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4