Tiny Lizard

Power Pivot Consulting and Training

  • About
  • Blog
  • Power Pivot
  • Training
  • Contact

DAX KEEPFILTERS Plus Bonus Complaining!

July 20, 2017 By Scott Senkeresty 5 Comments

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! Open-mouthed smile

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… imageand 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.image

[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… imageso, 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.   Sad smile

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… Winking smile

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

image

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 Smile with tongue out

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?

The streak is alive!
VAR: The best thing to happen to DAX since CALCULATE()

Filed Under: Power Pivot

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 278 other subscribers

Comments

  1. Philip Chan says

    July 20, 2017 at 8:36 am

    Always great to see a new post at Tiny Lizard. I think everyone shares your frustrations with DAX from time to time, as great a language as it is to use.

    I did come across the issue with measures having an implicit calculate on it (even though it’s in the PowerPivotPro cheat sheet for DAX), but the issue you mentioned that there’s no way not to implicitly calculate the function with the context transition is actually a really good point. VAR is only in PBI/Excel 2016+, so if you’re on Excel 2013 or earlier it can cause all sorts of problems.

    Reply
  2. Kenny McMillan says

    July 20, 2017 at 9:41 am

    Nice article and nice description of KEEPFILTERS:-)

    Reply
  3. Oxenskiold says

    July 20, 2017 at 5:47 pm

    This is a splendid blog post Scott!

    First of all any post on KEEPFILTERS is always welcome and expands the DAX knowledge accordingly.

    Your KEEPFILTERS(Dates[Year] = 2003) I bet will be a new to many. Even though a bit silly so would KEEPFILTERS(ALL(Dates[year])) and in a Mark-as-Date-table-scenario a KEEPFILTERS(DATESMTD(Dates)) could void something time intel related in a CALCULATE/-TABLE.

    Secondly, your complaint section really ought to give rise to a lot of great DAX discussions.

    Thanks Jes

    Reply
  4. 100tsky says

    July 22, 2017 at 3:02 am

    thank you

    Reply
  5. Ted Eichinger says

    August 4, 2017 at 6:08 pm

    I know enough to get myself confused!
    I’m trying to understand filter context. In the very first example I “think” I get it. I did my own table and it works.
    However I made a second table called Years(very simple), looks like this:
    Year Year Long
    2001 First Year
    2002 Second Year
    2003 Third Year
    2004 Fourth Year
    I make the relation Dates[Year] to Years[Year]
    So in your first example I replace the Dates[Year] column with Years[Year Long]
    I unexpectedly only get data for the [2003 Sales] measure only for “2003 Third Year”, not the whole column??
    I expected the relationship would flow down from Years[Year Long] to Dates[Year] that is being used in the [2003 Sales] measure. This does not appear to be happening.
    What is DAX doing, or what is the context that I’m not understanding?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Blog Posts

  • The streak is alive! August 10, 2017
  • DAX KEEPFILTERS Plus Bonus Complaining! July 20, 2017
  • VAR: The best thing to happen to DAX since CALCULATE() July 11, 2017
  • Review: Analyzing Data with Power BI and Power Pivot for Excel June 20, 2017
  • Power BI Date Table June 2, 2017

Follow on Twitter

My Tweets

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 278 other subscribers

Copyright © 2023 Tiny Lizard