I can think of two big ones off the top of my head.
Bioshock
Mass Effect
Bioshock had an amazing level of atmosphere and incredible art direction. That alone should have endeared it to me at least somewhat. However, the story felt weak and the gameplay even more so. Still, I'll defend the game as brilliant.
Mass Effect is a space-opera featuring epic space conflicts, (mostly) character-driven drama, and an expansive, eclectic assortment of alien creatures taken right out of Star Trek and Star Wars. It should have been a game right up my alley. Yet, I can't stand the games. The story and gameplay just fall flat for me. Not to mention some very weak and over-abundant dialog.
Honestly, I should absolutely adore both of those games. I really should. But odd and often bad game design choices just ruined the experiences for me.
I wanted to love them. Just couldn't do it.
[Honorable Mention]
Starcraft 2. I love the hell out of the first. And while the sequel is still "mostly" fun, everything beyond the occasional skirmish game is awful. Just awful.
The campaign was ungodly weak and the front-end of the game, as a sign of the direction Blizzard is going (exemplified in Diablo 3), is a test in fortitude.
Blizzard - You people want LAN support? HA! Fuck that! Do you really think we want to give the fans of this series the things they want and like from the first game? I mean, it's not like having LAN support was a key factor in making the first game as popular as it was or anything, right!?
Heh, right?
[sub]Right?[/sub]