PowerPivot's Filter Context from a SQL perspective

I figure I best write down my thoughts before my new found wisdom escapes me. Every time I think I've finally understood "Filter Context", it turns out that I really don't get it.

To be honest, I've read many definitions of "Filter Context" and descriptions about "Filter Context". None of them have helped me to master the concept. I don't come from the world of the multi-dimensional language MDX. Until recently, I didn't see the usefulness of PivotTables when a solid SQL query can provide the same information.