The thing that makes this card so good is that it is such an end-all solution to so much pain. Scenarios tend to throw a limited amount of pain your way (a scenario is only going to last so long, you'll only draw so many treacheries and face so many enemies), cards that single-handedly solve a defensive problem are very strong, for the reason that they are very effective for their slot. First Aid for example (probably) completely negates your risk of death from damage or horror (or both), although it will cost you 4 actions and 2 resources. Hallowed Mirror does the same with some slot restrictions and the need to draw the cards, but completely solves either damage or horror as a problem for that investigator or a friend.
Deny Existence not only saves you pain, it removes it! An effect that deals you 2 horror, heals 2 horror instead. This is a swing of 4 horror! Often enough to get you out of the risk-zone for the rest of the scenario. Also this is just a fairly average scenario, the crazy crap that starts to happen when you're negating the loss of resources, instances of 3 horror or damage ETC it can get brutally good. Also note that those other cards are generally doing this in 3-4 actions, Deny Existence is free to play and "fast".
This is the very best emergency button card in the game, barring perhaps only Ward of Protection in the instance where it discards a big enemy.