If you can deal with the action both Max Payne and Max Payne 2 have good and emotional stories. I really liked the story in The Moment of Silence (point and click adventure game). Not as emotional as Dreamfall, but great if you like sci-fi.
I second Grim Fandango, and Deus Ex if you like conspiracy thrillers/sci-fi and are ok with either stealth or action (it's your choice, the game can be completed with as few as three (scripted) kills, only two of which involve action).
Humn... I see a trend here. All the games I've mentioned involve conspiracies of some kind.