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.

We start again by loading data into PowerPivot:

We want to exclude certain rows in this set, I want to be able to exclude sales from country’s in a specific month and year. So I created a table in excel which contains the values i want to exclude on:

Again we load this to PowerPivot with create linked table. Ok so how are we going to check if we need to exclude these rows. To get data from two tables we need to create a relationship, and like in the Combine two files (or fact tables) in PowerPivot using relationships blog post we need to identify the unique columns, in this case again the Year, Month and Salesterritory, So we create a key based on these 3 columns in both tables:

KeyValue =Data[CalendarYear]&Data[MonthNumberOfYear]&Data[SalesTerritoryCountry]

And we create a relationship between them with the exclusion table as lookup table, this table has a unique keyvalue column containing each value once.

Ok now we want to get the sum of sales from rows that are not in the exclusion table. We can do this by using DAX  table functions. By using the RELATE function we can get the value of a column from a related table, so when we would do a RELATED(Excludedvalues[KeyValue]) in our fact table we can get the keyvalue of the excludedvalues table. This will return a blank value if a value cannot be found in the related table. We can use this in our fact table to determine if the value should be excluded. This would result in the following calculated column:

ValueNotExluded =if(ISBLANK(RELATED(Excludedvalues[KeyValue])),Data[Sales], BLANK())

This checks if a the keyvalue is not included in the related table and if that is the case show me the value from my measure field else return a blank value. This looks like:

As you can see some values are now blank. we now can use this calculated column inside your PivotTable:

As you can see you can do great things with DAX making it very easy to implement in all kinds of scenario’s.  This is another great example of where  you can use PowerPivot for!