DAX KEEPFILTERS Plus Bonus Complaining!

I cannot tell a lie.  Nah… that’s a lie.  I just lied.  But I am speaking truth here… I’m excited to write this post today!  I get the rare joy of sharing something cool with you… while doing my favorite thing… complaining about stuff!

We are going to talk about the KEEPFILTERS function.

Before I get too far… let me point you over to Alberto’s post on KEEPFILTERS.  Alberto makes what I think might be a mild overstatement “If you don’t use and understand it, you will incur major problems with your formulas and debugging the wrong results will turn into a nightmare”… but I do think that understanding KEEPFILTERS can help you understand the CALCULATE function… which is easily the most powerful and important function in all of DAX.

And hey, you might find a use for it… so let’s check ‘er out…

CALCULATE

The job of KEEPFILTERS is to modify the behavior of CALCULATE, so we need to understand that default behavior first.  Apparently, I wrote about CALCULATE back in March of 2014… I’m terrified to re-read that post, so I’m not going to.  You feel free, just don’t tell me if it’s crap.  The important thing here is that CALCULATE modifies the filter context… and that the filter parameters to CALCULATE overwrite the initial filter context.  So for example…

[Total Sales] := SUM(Sales[ExtendedPrice]) [2003 Sales] := CALCULATE([Total Sales], Dates[Year] = 2003)

Looking at the first row… the initial filter context is that Dates[Year] = 2001. But the filter parameter to CALCULATE has an opinion on the matter… and says Dates[Year] should be overwritten to be 2003 instead. And that works just lovely and you can see that in fact every row regardless of initial year… is showing the sales in 2003.

This is the default behavior.  If the initial filter context and filter parameter to CALCULATE both have an opinion… CALCULATE “wins”.  It overwrites the initial filter context.

KEEPFILTERS

The job of the KEEPFILTERS function is to change the above default behavior… such that instead of overwrite it changes it to combine… which is to say it keeps the old filter.  KeepTheInitialFilters… KeepFilters.  Good name really.

[2003 Sales with KeepFilters] := CALCULATE([Total Sales],
  KEEPFILTERS(Dates[Year] = 2003))

You can see the results at right look different than the table above without the KEEPFILTERS call.  Looking again at the first row… the initial filter is still that Dates[Year] = 2001.  Again, the filter parameter to calculate has an opinion about Dates[Year] and wants it to be 2003… but with the addition of KEEPFILTERS this time we combine the filters… Dates[Year] = 2001 and Dates[Year] = 2003. You will find this shocking, but no transactions happened simultaneously in both years, thus… we get a blank value.

That is maybe… interesting? Not terribly useful.  You would get same results with CALCULATE([Total Sales],  FILTER(Dates, Dates[Year] = 2003)).  meh.

Example

Without getting into some weird asymmetrical hierarchy like Alberto’s post… let’s see if we can’t find a more practical example.  (well, ast least… vaguely kinda sorta almost practical?).

I’m curious how many people have birthdays this month…

[Birth Monthers] := CALCULATE(COUNTROWS(Customers), MONTH(Customers[BirthDate]) = MONTH(TODAY()))

Cool.  I’m also interested in “Young Customers”.

[Younglings] := CALCULATE(COUNTROWS(Customers), DATEDIFF(Customers[BirthDate], TODAY(),  YEAR) < 40)

Cool.  Now… I’ve decide that I am interested in Young Customers … Born this Month.  Well, okay… we have 3 choices I guess?  Write a version totally independent of the above two measures, write a measure based on Birthmonths, or write a measure based on Younglings.   Well, we want to re-use as much as possible… so, let’s just pick…

Young BirthMonthers:=CALCULATE([Younglings],  MONTH(Customers[BirthDate]) = MONTH(TODAY()))

And off to the right, you can see the results in the Calculation Area in Excel… um, call me crazy, but I really doubt that every single Customer that was born this month was also under 40.  aka “This didn’t work”.   Why? 

Well, up at the top in bold “the filter parameters to CALCULATE overwrite the initial filter context”.  Okay, so… in the Calculation Area the filter context is just … whatever filters I have done on the tables in the Diagram View… and I don’t have any.  So, here are the steps:

1. We start with no filter context
2. The call to CALCULATE in the [Young BirthMonthers] measure modifies the filter context to just Customer with a [BirthDate] in July.
3. We then evaluate the [Younglings] measure… using the filter context from step 2!
4. Okay, [Younglings] also uses calculate, and it too has an opinion about filtering Customers[Birthday] and it overwrites the filter context… so, gone is July births, and now we are back to just people under 40.  

That is not what we wanted.  We wanted to Keep the Filters on the Customer[Birthdate] and just add some more filtering… if only there was some way…

Younglings:=CALCULATE(COUNTROWS(Customers), KEEPFILTERS(DATEDIFF(Customers[BirthDate], TODAY(),  YEAR) < 40))

We simply tell the Younglings measure “Hey dude, when you go to evaluate the filters… instead of replacing the initial filter context… just add to it”.   And <poof!> the results at right.  Turns out only 63 customers were born this month that were under 40!

It’s a bit interesting to note that… [Young Birthmonthers] gave us the wrong result, but to fix it… we had to update the [Younglings] measure.  That’s the measure where we wanted it to keep filters.

Cool?

Complaint #1:  Measure That Return Tables

Really, here is how I want to write these measures:

GLOBAL YoungCustomers = FILTER(Customers, DATEDIFF(Customers[BirthDate], TODAY(),  YEAR) < 40))GLOBAL BirthMonthCustomers = FILTER(Customers, MONTH(Customers[BirthDate]) = MONTH(TODAY())))[Younglings] := CALCULATE(COUNTROWS(Customers), YoungCustomers)[Birth Monthers] := CALCULATE(COUNTROWS(Customers), BirthMonthCustomers)[Young BirthMonthers] := CALCULATE(COUNTROWS(Customers), YoungCustomers, BirthMonthCustomers)

No code duplication, no keepfilters, super readable, re-usable and awesome.

But alas, you can’t write a measure that returns a table, there are no GLOBAL variables, this is just… not possible today.

I blame Miguel Lopez, but… that’s beside the point.  We still love him.

Complaint #2:  Power BI Desktop – No Filtering / No Calculation Area

In Excel, I use the calculation area all the time.  And I filter my data in the data view… all the time.  It’s incredibly useful for validating and debugging measures and understanding your data.  And can I just say that understanding your data is the whole reason we do this!?  That Power BI Desktop does not include these “features” (which to me is like saying… typing words is a feature of word processor) is completely insane.

So far 713 people agree with me.

Complaint #3:  Other ways to modify expressions?

I admit this is kinda rare… and it is honesty WAY less of an issue with the addition of VAR.  But imagine I have…

[Avg Sale] := AVERAGE(Sales[ExtendedPrice])[Above Avg Customers] := CALCULATE(COUNTROWS(Customers), FILTER(Customers, [Avg Sales] > AVERAGE(Sales[ExtendedPrice]))

This works like you would (maybe possibly) expect.  I checked in the Calculation Area in Excel, because Power BI Desktop doesn’t let me do that

It will give me the count of customers whose average sale is higher than the (global) average sale.  But we should feel vaguely weird… that [Avg Sales] behaves differently than AVERAGE(Sales[ExtendedPrice]).  This is specifically because [Avg Sales] … being a measure… has an implicit calculate.  But what if I don’t want that!?   I really want to re-use my [Avg Sale] measure because I love reusing stuff… I’m lazy!

I think an argument can be made that … measures should not have had an implicit calculate, because… well, it is pretty easy to explicitly wrap a measure in a calculate, ya know?  But there is no way to go the other way.  But that ship has kind of sailed.

So, how about this:

[Above Avg Customers] := CALCULATE(COUNTROWS(Customers), FILTER(Customers, [Avg Sales] > NOCALC([Avg Sales])))

Okay, NOCALC is a terrible name for the function, but in as much as we have functions (KEEPFILTERS) that modify how an expression is evaluated… I don’t think the over all idea here is crazy.  Some function that says “Hey, don’t do an implicit calculate causing the row context to filter context transition”.

Like I said, this isn’t a big deal… because how often does this REALLY happen to people… and more importantly, the addition of VAR makes this much better anyway:

[Above Avg Customers] := VAR GlobalAvg = [Avg Sales] RETURN CALCULATE(COUNTROWS(Customers), FILTER(Customers, [Avg Sales] > GlobalAvg))

That is easier to read, doesn’t have weird context transition issues and is more efficient… so, ya… ignore this complaint, maybe?