"Heroes are supposed to win, what if I lose?" While I can't believe I am doing it, that quote is from a movie called Varsity Blues, and in a sense sums up the problem that you can face at times when presenting your players with a Desperate Situation. You know the kind of situation I am talking about, almost every action movie ever made has one. You have to go in and save everyone, only it's suicide to even consider it. No way 4-6 people, no matter how good will be able to get in there. You're going to be flying fighters in an attempt to take out a Battle Station capable of taking out planets, a battle station that is packed full of wings upon wings of opposing fighters mind, not to mention the on-board defenses. You have to go and beat the unbeatable fighter, take on a god, whatever. These are the things that movies and stories are full of, and yet I've seen more games than I'd care to admit fumble when it comes to even coming close to these things.
Why? Well, there are a couple of reasons. One is that a lot of GMs don't like setting up situations that are more likely to kill the whole group than be beaten by them. That isn't even an unfair assessment to have either, how often have I said not to railroad and to gauge the challenge to the group here? Add to that that Players often will be rather vocal about their complaints at the so called unwinnable situation before them and it is something a lot of GMs try to avoid. Another is that when the GM does set one up, the players simply won't go for it. Or they'll do everything in their power to not make it so desperate, and who can blame them? After all, this isn't a movie or a comic book, in a RPG the players can lose.
So what is the problem with this? Well, two fold. First, the fear of losing is a deterrent to these situations when really it should be the opposite. The GM doesn't want to do it for fear of a party wipe, the players don't want to go into it for fear of losing as well. However, that very fear is where all the drama comes from. How many people that played Mass Effect 2 found the ending more tense because they knew that people, even Shepherd him/herself could and would die if they did something wrong? How much drama in those movies and TV shows comes from the fact that the characters understand that they very likely will lose, and them trying to fight against all odds to make it through. Overcoming this is the first step towards getting the desperate situation to work in a game, for it to be looked forward to instead of something to dread and hide from.
So how do you do it then? Well, first of all you need to put the Players into a situation where they want to go into the desperate situation. Put something that they care about on the line. A desperate situation for the sake of a desperate situation sucks. So make it worth while. They have to save someone they care about, the army is marching on their home base and it is time to make a stand. Something, anything, but make it worth it. There needs to be significant and worth while gains for pulling off the situation. Like I said, you need to make it worth it to them, or they're not going to go into it. At the very least, they're not going to go into it expecting anything other than "So this is how the GM is going to end this and start that new game he's been talking about....". Make it worth it though, and they'll jump right in sure that if they do it right the rewards will be worth it.
Secondly, you need to make it winnable. Now granted you need to play up the desperateness of the situation, but it also needs to be a winnable situation. This will tax your ability to set a challenge to the max. They need to know that a mis-step is doom, but they also need to clearly see where they can put their next step to keep themselves safe. You may need to fudge rolls a bit here to help remove the luck factor, though generally I recommend against it. Just be careful with when you hit them and how. The idea is to keep the tension on, but not to grind them down into dust or overwhelm them. After all, if they give up you are just not going to have the same situation.
Thirdly, if someone does die, and let's face it that is a very probable happening when it comes to these kinds of situations, you need to make it special. Tell your players straight up that they can die, but if they do it is going to be special. So a death in combat may not take effect until a bit later in the adventure. Don't just gun someone down in combat, have them save someone's life, or die watching everyone's back and buying them more time. If you have a large group (like 8+) a death or two in the middle of things can add to the tension, but if it is a normal sized group make those deaths special. A character going out in a Desperate Situation should go out with a bang, a death worth remembering. So give them that.
Fourthly, and perhaps most importantly, never force them into the desperate situation. A desperate situation that they go into on their own, sure that is fine, it happens and if things go wrong it was their choice. A desperate situation that they're forced into with no way out? That isn't a choice, they'll feel trapped, you'll lose trust with your players, and the session will dissolve into something that is much less than fun. So give them a way out, even if there are only two choices. Yes there will be consequences for those actions, but not necessarily insurmountable ones. Look at A New Hope (yes I love Star Wars, I get it), in the end of the movie they have the desperate situation of the Death Star coming for them, they make the choice to fight. They could make the choice to flee, save as many as they could. Only, they would lose the base, lose a lot of people (not everyone could make it out), and eventually they would have to deal with the death star. So they chose to fight, instead of deal with the consequences, but it was still their choice, and that is what is important.
So keep those four things in mind when you are thinking you want to add a real dramatic moment to the game. They're not hard to remember simply make it worth it, make it winnable, make sacrifices/loss matter, and make it their choice. You do that, and even if it turns into a Total Party Wipe it will be a memorable one. Some of my players still talk about my last party wipe, despite the odds against them that you could claim I unfairly put them against. Why? Because in the end it was their choice to engage, and they performed a great deed by doing so, the deed just cost them all their lives.
So like I said, keep it in mind.