I've only ever accidentally turned it on and hated it every time.
I liked motion controls for throwing grenades in the first Uncharted, and also tilting it to jump backwards whilst hanging. I didn't enjoy it as anything more than a novelty when it came to steering in Motorstorm though.
I like motion controls as an additional, optional input. Hopefully, now that the Switch is out and selling well, we'll see more developers embracing such additions in multiplatform games.