Prologue(A few, actually!)
You finally found it, the ultimate weapon in the capital wasteland! The Experimental MIRV! It came with FIVE mini nukes, booya, giving you eight nukes after the three you found yourself down to. You go outside, happy, grinning widely, looking for those deathclaws to blow up you came across earlier. You take aim upon finding them, fire, and blow one of them up. The other comes, no worries! You got more ammo! Right? Wait, you have no mini nukes!? Oh no! Now your dead! You reload, and thinking it's a glitch, try again, fire more to ensure the other deathclaw is hit, but wait! A nuke landed to close, you died again.
You finally use the CAIN in Mass effect 2. You test fire it on a sub boss, and find out the sad truth of the weapon, you have no ammo for the real boss, ooops. You try to keep it with you in more missions, but find that there isn't even enough AMMO for the thing in most missions. Grief, what a waste.
You try building a battle cruiser armada in Starcraft 1 after playing a non big game hunters map for the first time, run out of minerals and gas in record time, and get crushed by a player laying siege tanks and goliaths outside your base in far larger numbers than your cruisers can handle. You try it in starcraft 2 and discover the same problems with players either destroying you before you build them, or running out of resources before you can build enough.
You spend 6 powers in CO at the low levels and finally unlock force cascade, try to fire it, and discover even with endurance primary it's largely unsable. Mobs have plenty of time to mow you down whenever your building energy up, so you respect for recovery to, but still discover downtime. Then you discover that many mobs only survive a half charged shot of the power. You spend a lot of time building for it, but then when you try to take it into anything with other people firing on the mobs, force cascade falls so far behind the DPS curve due to it's cost. Better off sticking with Arc lightning or pulse beam rifle.
This, is what awesome yet impractical is: Everything else in the game gets the same results cheaper and often more efficiently, inspite the often lower stats than whatever makes it "awesome". In the Experimental MIRV's case, it's sheer ammo use results in it excessively over-killing while also leaving the user with no ammo unless they cheat. The user is also likely to blow themselves up with it. In the case of Mass effect 2's CAIN, it also uses up far to much heavy weapons ammo to be useful in the actual game. Starcraft 1's heavy units are often notorious for costing to many minerals/gas in competitive games, and arriving to late to be of any use.
In CO's case, unless your already level 40 and have numerous cost reduction and maxed endurance/recovery, force cascade and many high cost powers fall into this trap, to. They are unusable for anyone not 40, as so many other abilities get the same results. Even at level 40, one is often better off sticking with single target powers like arc lightning/PBR on legendaries, because a: they are cheaper and b: they often do more damage. The new Two gun from what I'm reading falls into that, hell the cost is the same as bloody lightning storm apparently. Why? It does LESS damage when it hits multiple targets.