Conditional location slice

[Robblaze]

Hoping someone can help, I’m having trouble wrapping my head around how to filter this slice.
I have multiple columns, relevant are [UPC], and [Location]. All items in the database have their location set as either Sales Floor, Sold, or Bin A (Bin B, Bin C, etc,) Regardless of Bin location, they’re all considered overstock. There can be any number of duplicate UPC’s, with a maximum of 1 of them labeled as Sales Floor, and the others divided between Sold and the various overstock locations.
I have a slice that only shows Sales Floor items, another that shows Sold, and another that shows overstock, those were easy.
What I need next is a restock slice, that lists all UPC’s in overstock, that don’t also appear in Sales Floor. Basically, the overstock slice minus the sales floor slice. Ideally, it would only generate a single instance, if there were multiple overstock locations. Random among these is fine, as long as its just 1.
Example: UPC 1234 has 6 rows, 1 is Sales Floor, 1 is Sold, and 4 are overstock. Slice doesn’t include this. UPC 4567 has 5 rows, 1 is Sold, 4 are overstock. Slice does include this, but only shows 1 of them from overstock, not all 4.
I believe I need to use a combination of Not() and In() applied to a list of [location]=“Sales Floor”, but so far I’m hitting a wall.
Hoping someone has an idea, this feels like it should be simple but I can’t wrap my head around it.

[Marc_Dillon · Accepted solution]

Here is what I think I would do:

First create a “potential restock” slice. Basically, any UPC that is in overstock, where the same UPC is NOT in sales floor.

AND(
  CONTAINS( [location] , "Bin" ) ,
  NOT( IN( [UPC] , Sales_Floor_Slice[UPC] ) )
)

Second, create your actual “restock” slice, that just picks one record from the potentially multiple of the same UPC in the first slice.

[_THISROW]
=
MINROW( 
  "potential_restock" , 
  "_RowNumber" , 
  [UPC] = [_THISROW].[UPC] 
)

---

Now, I only thought about this for 5 minutes or so, so I’m not guaranteeing it is the best way, but I think it should work. Feel free to replace MINROW with MAXROW, or just do an ANY(SELECT(…)).

help.appsheet.com

MINROW()

Row with lowest value in column

[Robblaze]

Thank you!
That worked perfectly. I changed the first slice slightly, to
AND(
not([location]= “Sales Floor” ),
not ([location] = “Sold” ),
NOT( IN( [UPC] , Floor Inventory[UPC] ) ))

I also omitted the second slice you suggested, after trying this and creating a view grouped by locations, I like the multiple options better than unique values since it lets me pick and choose which bin to pull from. Your answer was perfect, so thank you