In the Kelvin Timeline, Kirk failed the Kobiyashi Maru and keeps retaking it. (In the film, it’s implied that he’s making the rest of the bridge crew take a couple of hours to act out their roles in the simulation each time, and that he’s done this over and over, an unusually large number of times.) He doesn’t even pretend to be taking it seriously or in any doubt about the outcome. In this timeline, cheating by hacking the computer that gives the test gets him court-martialed, and he would have been expelled from Starfleet Academy if not for an incredible streak of coincidences. I would say that constitutes an absolute failure.
Kirk-Prime had a similar idea but got commended for it. The EU explains this by saying he approached it differently:
He programmed the Klingons to be in awe of the great Captain Kirk, and explained that it was realistic because he would earn himself that kind of reputation in real life. (It is totally consistent with TOS that Starfleet was looking for that kind of narcissism in its commanding officers.)
By the time of TNG, we do see a similar test that someone fails, but with no further consequence than being refused a promotion. (Starfleet’s attitude toward personnel retention is the very opposite of up-or-out.) In “Thine Own Self.” Deanna Troi takes the Bridge Officer’s Test for promotion to the rank of Commander. (That officers in what would be considered the support branches in the US military today are directly in the line of command in Starfleet and take over if they are the highest-ranking officer present is another of the few differences between the command structures of the twentieth and twenty-fourth centures.) Troi passes every stage of the examination but the last, the Engineering qualification. This is a holodeck simulation where she is the highest-ranking officer in Engineering as the ship is breaking down and they lose communication with the rest of the ship. Regardless of what Troi tells Sim-Geordi LaForge to do, the warp core breaches and the ship is destroyed.
After Troi has failed three times (the scenario becoming harder each time), Will Riker finds her studying the details of warp cores and tells her that he’s cancelling her exam. “Deanna,” he says, “this is nothing personal. Not everyone is cut out to be a Bridge Officer. I don't think this is for you.” She asks, “Why? Because I'm not the most technically-minded person on the ship? I may have trouble telling the difference between a plasma conduit and a phase inducer, but there's more to being a bridge officer than memorising technical manuals.” She even subverts viewers’ expectations a bit by asking, “Is there a solution? Or is this simply a test of my ability to handle a no-win situation?” Riker tells her that there is, but he can’t reveal it.
A remark Riker made gives her enough of a hint to go back and pass the test. Starfleet isn’t really training a ship’s counselor to micromanage its professional engineers in a life-threatening emergency just because she happened to be nearby at the time and technically outranks them. She realizes that this scenario is a different kind of no-win scenario. (Although no one in the episode makes the reference explicit, the other one from The Wrath of Khan.) Even though the automated safety systems aren’t working in the simulation, sim-LaForge would be able to save the ship if he entered the warp core himself, but the radiation would kill him. Troi orders the sim who looks just like her friend to do it, and passes the real test.
Since she was able to take the test again even though he told her he was failing her, it doesn’t appear that the examiner actually can fail the candidate until she herself gives up. (Or perhaps he had put off actually doing it.) It is implied that she would be disqualified if one of her friends gave her the solution. The motif that no one can be told the answer to her test of character works well in the episode, but if you stop to think about it, it doesn’t make a lot of sense as world-building. (The only time in the history of Star Trek that’s ever happened!)
By this time, everyone apparently gets personalized tests on the Holodeck. Wesley Crusher’s Psych Test to enter Starfleet Academy, in “Coming of Age” intentionally duplicates the situation where Jean-Luc Picard was forced to let Wesley’s father, Jack Crusher, die. (Because this was first-season TNG, it misses the opportunity to have either of them make a less-than-perfect decision, explore how two different answers could both be justified or at least understandable, or have any tension between the characters.) In that instance, the test-giver reveals how the test was being scored: to pass, Wesley Crusher needed to save someone, even though he couldn’t save both, and needed to be able to explain his action somehow. Failure would have meant rejection from Starfleet Academy, ending his career before it had begun.
Although, in the end, he is passed over for another reason, but allowed to reapply the next year.
Dr. Sheldon reminds us that, in the TOS episode “Bread and Circuses,” Kirk runs into a former classmate who “was dropped in his fifth year because he failed his psychosimulator test.” That sounds very similar to the “psych test” Wesley Crusher took.
Since you edited the question, there is another failure like that from TNG—except that it wasn’t Starfleet that created the scenario. It happens to Jean-Luc Picard in “Tapestry.” Picard is dying because of his bionic heart, which he needed because he had been stabbed through the heart in a bar fight as a young man. Q appears to him and, after some rounds of verbal sparring, gets Picard to say that, yes, he did some things back then that he regrets. Q offers him a second chance, with a promise that the ripple effects won’t have a major impact on anyone else.
The result is that Picard “never had a brush with death, never faced his own mortality, never realized how fragile life is or how precious every moment must be,” so he always played it safe. He’s now a low-level paper-pusher on the Enterprise and, when he asks about promotion, is told it’s not in the cards for him. (It would have been especially ironic that Troi is the one to tell Picard this had Patrick Stewart been available while “Thine Own Self” was being filmed and Picard had given her the evaluation Riker did.) “Hasn’t that been the problem all along? Throughout your career, you’ve had lofty goals, but you’ve never been willing to do what’s necessary to attain them.” Riker agrees, although he adds, “We don’t want to lose you. You’re a very good officer.”
And if we really want to stretch along those lines, one of the diabolical ways the Cardassians turn double agents is to put them in holodeck simulations so realistic that they can never again be sure they ever really got out. At one point, they even tried to gaslight Kira Nerys that she had always been a Cardassian spy and that her memories were fake. That could certainly qualify as a no-win simulation that could end your career in Starfleet!
