Interesting. I would consider it quite inappropriate to tell someone to "cheer up" if a family member had passed away. Essentially, you are telling the person to stop being sad, and try to be happy. It seems wrong to tell someone to be happy if someone close to them has died. I think most people would say you *shouldn't* be happy under those circumstances.
I agree with Frank P. that the tone is important, because depending on the tone, the phrase could sound less like "be happy" and more like "don't let this completely ruin your life." But personally I don't think I would ever use this phrase if I were talking to someone in a really serious situation. To me it seems better for circumstances where you're trying to convince the other person that what happened wasn't really so bad. Maybe the person lost a competition, or failed a test, or had a fight with a friend, or didn't get invited to a party, or has to do something unpleasant. "Cheer up, you can always try again / it's only for a little while / it was probably an accident / everything will work out / etc."