Reports

create or replace table project.dataset.table
as 
WITH tmp AS (
  SELECT 1 AS id, "abc" AS txt, null AS result
  UNION ALL
  SELECT 1 AS id, "Passed" AS txt, "Passed" AS result
  UNION ALL
  SELECT 1 AS id, "def" AS txt, null AS result
  UNION ALL
  SELECT 2 AS id, "hgi" AS txt, null AS result
  UNION ALL
  SELECT 2 AS id, "jhg" AS txt, null AS result
  UNION ALL
  SELECT 2 AS id, "Failed" AS txt, "Failed" AS result
)
SELECT
*
from tmp

I can post the code here better than in my comment, I created a table (above)

CREATE OR REPLACE MATERIALIZED VIEW project.dataset.table

AS
SELECT
    tmp.id,
    tmp.txt,
    tmp.result,
    tmp2.result AS i_want
FROM project.dataset.table as tmp
    INNER JOIN tmp AS tmp2
        ON tmp.id = tmp2.id
WHERE
    tmp2.result IS NOT NULL

Then tried to run the query as a materialized view but it will not work unfortunately.

79533417