I distinguish between heroic sacrifice endings and tragic endings.
Tragic endings are characterized by inevitability. The hero marches inexorably towards their doom, because circumstances conspire again them, conflicts of duty, their own flawed nature and similar reasons. In a "good" tragic ending, the hero knows this and eventually accepts their fate, and does something great that will be remembered before the inevitable demise. A game with an ending like this can be made, but the circumstances for the tragedy must be known in advance, convincing, and be reflected upon. Tragedy aims at catharis, an emotional cleansing, and if it works, it makes you feel empty but weirdly satisfied. If it fails, it leaves you depressed.
Heroic sacrifices are characterized by the fact that the hero chooses the action that will result in their death. There is no inevitability, in fact, it is exactly to avert what would otherwise be fated that the hero makes the sacrifice. For a game, that means that if it isn't made by choice, it isn't a heroic sacrifice. Heroic sacrifices are intended to be uplifting and if they work, they make you feel as if touched by something sublime. Falure can have many results, but typically results in confusion if the sacrifice was poorly rationalized or depression if you feel it wasn't worth it.
Personally, I like neither but I'd be willing to accept a tragic ending if it's foreshadowed well and early enough, I can get my protagonist to reflect upon it and the circumstances are convincing. The problem with doing this in a game is that it *must* be reflected on to work, and some characters we play aren't the type. For that reason, I think this will work only in games where the character is very defined from the start and there's never the illusion they're yours. It might have worked in ME3 had ME3 been the whole story, since Shepard was so much more defined there, but with two predecessor games which were made to make our character ours it was doomed to fail.
As for heroic sacrifices, I greatly dislike them and I almost never play them because most of the characters I play just aren't the type. They're also often tainted by the hint that it's my moral obligation to sacrifice myself, and I do not believe in such a notion. This is, in fact, the main reason why I tend to be hostile towards the idea. If such an ending arises naturally from the story, I like it if the option is there, but I will always tend to avoid it.
For DAI, this means that I will not accept an ending that inevitably entails my character's death. If it deserves the term "roleplaying game", then my character will be too much mine and the character types I want to play most will not be the type for either a tragedy or a heroic sacrifice. If the option is there, I might make a character who is the type in some replay or other because I do like to play something different once in a while, but I won't be forced into it with my mains.