Creating a measure in PowerPivot that excludes values that are in a second table (like SQL Not in)

I had a great chat with Michiel Rozema from Microsoft NL, who is addicted to PowerPivot like me  . We were talking about joining two fact table and how we could do this, I told him i had a blog post on the roll which you might have seen last week: Combine two files (or fact tables) in PowerPivot using relationships, relationships explained.

So i send him the link to this blog post,a few days later he came back to me on this that he had used this technique to do the opposite: he wanted to exclude values from a measure when they were included in a second table. This could also prove very handy and you might be using this often. So today we are going to take a look at using a relationship to exclude measures. 

Read more...