Invalid column for SELECT with OVER
WHERE runs before the window function. So run the whole thing in a derived table.

SELECT t.*
FROM (
  SELECT 1, 
    row_number() over (
      partition by lower(X), lower(Y) order by X DESC
    ) AS rn
  FROM dbo.MyTable
) AS t
WHERE rn = 1;