Whether or not a game must end on a positive note is subjective.
But games inherently reward players for playing. In fact that's why we find them so addictive. The thing about ME 3 is that it punishes players for playing at the end regardless of what you choose.
For example My original DA O playthrough had a terribly tragic ending. Warden falls in love with Morrigan, then refuses ritual, she leaves forever, he has already killed Logain so he is kinda in a box when it came to the final hour.
My warden CHOSE to kill the archdemon himself and die, rather than asking a friend to do so in his place.
For those keeping score at home. My warden died young, after his love left him, and went to his grave thinking all his family was dead (cousland). This is a horrifically tragic ending. Like just awful. I LOVED IT.
Why?
Because it was my choice. And it was heroic.
On subsequent playthroughs I lived by sparing Logain or doing the ritual, but the fact that I could choose what would happen made it better.
And therein lies the problem. Films are external, we see and hear things happen to SOMEONE ELSE.
Games that are RPG's are partially internal, my Shepard looks like me, makes the choices I would make, and picks the girl I would fall for. This means that for all intents and purposes, the world of Mass Effect is happening TO ME.
This is awesome but so rarely done well that most of us never really felt it before ME 3. Now think about this for a second.
In a dark, cynical film or a standard video game if the hero dies, it has happened to someone else. While sad this is hardly going to stick with you. Cause in point, while I was sad that Soap from CoD died, I didn't really care that much.
In an RPG the player IS the character. The character has almost no form beyond what I give him. Therefore I (and everyone else who plays ME 3) am Shepard.
In ME 3, no matter what I do, I ****ing die. I don't save hardly anyone (arrival proves that giant Mass Relay explosions kill), and all my good work is essentially undone. Notice the 'I's.
Shepard isn't failing. I am. I just failed so many people in such a real and graphic way that it could very well cause PTSD.
So while a game doesn't 'have' to end on a positive note, there had better be an option where 'I' don't completely fail.