Add a column whose value depends on another row's value

[whatdahil]

I'm trying to implement an "auto-fail" kind of column using table calculations that includes scores for evaluations like below:

Employee Category Score

John	Foo	5
John	Bar	4
Randy	Foo	3
Randy	Bar	0
Daniel	Foo	2
Daniel	Bar	1
Joe	Foo	1
Joe	Bar	0

I wanna create another column ('calculated score') such that, if their score in the Bar category is 0, they get a 0 on their overall score (basically, their score on Foo also becomes 0). Otherwise, it will just reflect their current score

So in the example above, the desired outcome will be:

Employee Category Score Calculated Score

John	Foo	5	5
John	Bar	4	4
Randy	Foo	3	0
Randy	Bar	0	0
Daniel	Foo	2	2
Daniel	Bar	1	1
Joe	Foo	1	0
Joe	Bar	0	0

Where Randy and Joe will now have 0 overall scores because they got 0 in Bar

Is there any way to create something like this in Looker using Table Calculations? I've been looking into different ways to implement something similar that will look into the whole table for values (like using OVER PARTITION in SQL) but I can't find any.

Thanks!