cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Can anyone explain why this works?

Hi experts,

I'm reading the tutorial here:

https://www.daxpatterns.com/new-and-returning-customers/

 

In the first meausre it presents:

 

 

=COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Sales[CustomerKey] ),
            "PreviousSales", CALCULATE (
                COUNTROWS ( Sales ),
                FILTER (
                    ALL ( 'Date' ),
                    'Date'[FullDate] < MIN ( 'Date'[FullDate] )
                )
            )
        ),
        [PreviousSales] = 0
    )
)

I don't understand why 

 

ALL ( 'Date' ),
                    'Date'[FullDate] < MIN ( 'Date'[FullDate] )

works. Since we already used ALL('Date') to remove all filters from 'Date', even after placing it into a pivotal table, wouldn't this always return an empty table?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Can anyone explain why this works?

@markus_zhang 

I see your point. I was similarly bewildered when first presented with this type of formulae.

 

There are two tricky subjects here, by order of importance:

1. What 'Date'[Full Date] actually refers to.

2. The nuances of how ALL( ) actually works     

 

The behaviour of ALL depends on where it is used. It should probably have two different names. Since you're already familiar with the Italian gurus, check out this article for details. ALL can work either by removing filters or by ignoring filters. It sounds like the same but it sure ain't.

 

Let us walk through your example:

The filter context

We have 'Date'[Full Date] in the rows of our pivot table. That determines the filter context. 

 

Inside FILTER( )

FILTER( ) first computes the table it will operate on. In this case, ALL('Date'). That is the full 'Date' table in your data model. Let's call it Table=ALL('Date').

Nota bene: ALL( ) here ignores filter context but it does NOT remove it, so filter context will be effective everywhere else.

 

Table has a 'Date'[Full Date] column as well, an instance different from the one in the pivot that we marked in red above. We'll use purple for Table's : 'Date'[Full Date]. So we have one name referring to two different instances. We'll have to see how we tell them apart.

 

FILTER( ) and filter context interaction

Once it has Table, FILTER( ) starts scanning it to check whether each row complies with the condition:

'Date'[FullDate] < MIN ( 'Date'[FullDate] )

 

'Date'[FullDate] is the value in the current row of Table. MIN('Date'[FullDate]), however, is the minimum of the values of 'Date'[Full Date] coming from the pivot table (filter context). This is the crux.

 

Conclusions

You do have a valid point in that MIN('Date'[FullDate]) could actually be referring to MIN('Date'[FullDate]), in which case every row would certainly be filtered out.

How does DAX discriminate between 'Date'[FullDate] and  'Date'[FullDate]?

Basically, when you use the "naked" column, i.e. the column on its own, it's 'Date'[FullDate]. In any other case you'll be referring to 'Date'[Full Date]. It's been built that way so that you can refer to both instances and build powerful code like the one you've shown.

As a final point, bear in mind that filter context affects both arguments in

     FILTER(<table>; <filter expression>)

In this case the filter context is ignored in the first argument because we are using ALL( ). It'd be another story if we were using VALUES( ).

 

Does that help? (I do hope so, as it took some time to put together Smiley Wink)

 

View solution in original post

9 REPLIES 9
Highlighted
Super User IX
Super User IX

Re: Can anyone explain why this works?

The ALL is there so that, regardless of visualization context, it gets all of the rows in the Date table. Then, it filters those rows down to the rows where FullDate is less than the current MIN of the FullDate as determined by visualization context.

 

So, in the visual, let's say that you have a matrix or table or column chart that uses Date, perhaps in the form of Month. For the Month of February in that visual, the MIN('Date'[FullDate]) will be 2/1/2018 let's say. So, what that filter clause does is make sure to eliminate all filter context from Dates and then imposes it's own so in the scenario above it will return all dates from 1/1/2018 to 1/31/2018.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User III
Super User III

Re: Can anyone explain why this works?

@Greg_Deckler

I think it would return all dates in the 'Date' table prior to 02/01/2018, not just those prior to 02/01/2018 in the year 2018. Or am I mistaken?

 

@markus_zhang

What makes you think it would always return an empty table?

 

Highlighted
Super User IX
Super User IX

Re: Can anyone explain why this works?

@AlB - Yes, correct, sorry I had an unstated assumption there that we were dealing with a date table that just had 2018 dates in it.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate II
Advocate II

Re: Can anyone explain why this works?

Thanks! The thing is, in FILTER():

 

FILTER (
                    ALL ( 'Date' ),
                    'Date'[FullDate] < MIN ( 'Date'[FullDate] )
                )

ALL('Date'), as you said, already removes all filter context regardless of Pivot Table (sorry forgot to mention this is in Excel 2016), then I think both 'Date'[FullDate] are under the same context (that is, no context at all), so it will always returns empty, as none of the FullDate is going to be smaller than the minimum of the FullDate.

 

However, as you mentioned, it actually means FullDate that are smaller than the current MIN(), which is influenced by the Pivot Table. This is the part that confuses me. If ALL() already removes the filter context, how come it comes back in the same function? And if it comes back, why does the first 'Date'[FullDate] not get influenced? For a calculated column, I'd use EARLIER() for the second 'Date'[FullDate], but I'm not sure how to do this in a measure.

 

 

Highlighted
Advocate II
Advocate II

Re: Can anyone explain why this works?

Hi AIB,

 

Here is my logic (details in another reply):

 

Since ALL() removes the filter context, both 'Date'[FullDate] should be under the same filter context (which is, no filter context at all), that's the reason I think it should return 0. After all, none of the element should be smaller than the minimum.

Highlighted
Super User III
Super User III

Re: Can anyone explain why this works?

@markus_zhang 

I see your point. I was similarly bewildered when first presented with this type of formulae.

 

There are two tricky subjects here, by order of importance:

1. What 'Date'[Full Date] actually refers to.

2. The nuances of how ALL( ) actually works     

 

The behaviour of ALL depends on where it is used. It should probably have two different names. Since you're already familiar with the Italian gurus, check out this article for details. ALL can work either by removing filters or by ignoring filters. It sounds like the same but it sure ain't.

 

Let us walk through your example:

The filter context

We have 'Date'[Full Date] in the rows of our pivot table. That determines the filter context. 

 

Inside FILTER( )

FILTER( ) first computes the table it will operate on. In this case, ALL('Date'). That is the full 'Date' table in your data model. Let's call it Table=ALL('Date').

Nota bene: ALL( ) here ignores filter context but it does NOT remove it, so filter context will be effective everywhere else.

 

Table has a 'Date'[Full Date] column as well, an instance different from the one in the pivot that we marked in red above. We'll use purple for Table's : 'Date'[Full Date]. So we have one name referring to two different instances. We'll have to see how we tell them apart.

 

FILTER( ) and filter context interaction

Once it has Table, FILTER( ) starts scanning it to check whether each row complies with the condition:

'Date'[FullDate] < MIN ( 'Date'[FullDate] )

 

'Date'[FullDate] is the value in the current row of Table. MIN('Date'[FullDate]), however, is the minimum of the values of 'Date'[Full Date] coming from the pivot table (filter context). This is the crux.

 

Conclusions

You do have a valid point in that MIN('Date'[FullDate]) could actually be referring to MIN('Date'[FullDate]), in which case every row would certainly be filtered out.

How does DAX discriminate between 'Date'[FullDate] and  'Date'[FullDate]?

Basically, when you use the "naked" column, i.e. the column on its own, it's 'Date'[FullDate]. In any other case you'll be referring to 'Date'[Full Date]. It's been built that way so that you can refer to both instances and build powerful code like the one you've shown.

As a final point, bear in mind that filter context affects both arguments in

     FILTER(<table>; <filter expression>)

In this case the filter context is ignored in the first argument because we are using ALL( ). It'd be another story if we were using VALUES( ).

 

Does that help? (I do hope so, as it took some time to put together Smiley Wink)

 

View solution in original post

Highlighted
Advocate II
Advocate II

Re: Can anyone explain why this works?

Thanks AIB, this makes everything clear.

 

"Basically, when you use the "naked" column, i.e. the column on its own, it's 'Date'[FullDate]. In any other case you'll be referring to 'Date'[Full Date]."

 

This is the key! Does that mean, whenever I use a function like MIN(), MAX(), FIRSTNONBLANK(), etc. to wrap 'Date'[FullDate], it ignores the ALL() effect?

Highlighted
Super User III
Super User III

Re: Can anyone explain why this works?

@markus_zhang

Kind of. The way you describe it is, being strict, innacurate but I think you get the point.  

The ALL( ) here provides the full 'Date' table as table to be scanned in FILTER(). Its effect finishes there.

The second argument in FILTER() is also affected by filter context but when you use the naked column 'Date'[FullDate] you are referring to 'Date'[FullDate], the column in the table being scanned, which we just said has all rows of 'Date'. If you wrap 'Date'[FullDate] in whatever, MIN( ), MAX( ), etc. the filter context applies because you are actually referring to 'Date'[FullDate].

Now think what would happen if instead of ALL( ) we had VALUES( ) as below. That will help you nail it. 

 

FILTER ( VALUES( 'Date' ),
                    'Date'[FullDate] < MIN ( 'Date'[FullDate] )
        )

 

Highlighted
Advocate II
Advocate II

Re: Can anyone explain why this works?

If we use VALUES() instead of ALL() then the current filter context should still be effective, so I guess it's completely different story. Thanks for the help!

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors