Fail to understand the behavior of ALL() Function

[Anonymous]

Hello,

I am facing a problem with relationships and FILTER direction behaviour.

I have three tables in the model - MonthYear, Dates, Table1 having below relationships among them 

I have created two measures for calculating Total Amount from Table1 as below:

Measure1 = 
VAR SelectedMonth =
    IF (
        ISFILTERED ( Dates[Month]),
        SELECTEDVALUE ( Dates[Month Num] ),
        IF (
            ISFILTERED ( Dates[Quarter] ),
            MAX ( Dates[Month Num])
        )
    )
VAR SelectedYear =
    SELECTEDVALUE ( Dates[Year] )
VAR EndDate =
    DATE ( SelectedYear, SelectedMonth, 1 )
VAR TotalAmount =
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER (
            ALL( Dates),
            Dates[Date] <= EndDate
        )
    )
RETURN 
   TotalAmount

Measure2 = 
VAR SelectedMonth =
    IF (
        ISFILTERED ( Dates[Month] ),
        SELECTEDVALUE ( Dates[Month Num] ),
        IF (
            ISFILTERED ( Dates[Quarter] ),
            MAX ( Dates[Month Num])
        )
    )
VAR SelectedYear =
    SELECTEDVALUE ( Dates[Year] )
VAR EndDate =
    DATE ( SelectedYear, SelectedMonth, 1 )
VAR TotalAmount =
    SUM ( Table1[Amount] )
RETURN 
   TotalAmount

The only difference between both the measures is - in Measure 1, I am applying the filter on Dates table and then calculating sum(amount) as

CALCULATE ( SUM ( Table1[Amount] ), FILTER ( ALL( Dates), Dates[Date] <= EndDate ) )

and in Measure 2, I am calculating only SUM ( Table1[Amount] ). 

Now what I fail to understand is, though my relationship between MonthYear table and Dates table is one-many, and the filter direction is from MonthYear to Dates, the filter condition in the Measure1 on Dates table is affecting the SUM(). This behaviour is causing different results for both measures. Please help me to understand this behaviour.

Please guide if I am missing something.

Below is the link of Test.pbix file:

Test.pbix

Thanks!

[AlB]

Hi @Anonymous 

This has mostly to do with expanded tables. Check it out here

Use the ALL on the date column only, rather than the whole table (which will affect trhe expanded table)

CALCULATE ( SUM ( Table1[Amount] ), FILTER ( ALL( Dates[Date]), Dates[Date] <= EndDate ) ) 

or equivalently:

CALCULATE ( SUM ( Table1[Amount] ),  Dates[Date] <= EndDate )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

Hi @AlB,

Thanks for the good explanation but I still have doubt as follows:

In this scenario, expanded columns in tables: 'Dates' will be 'MonthYear[MonthYear]' and in 'Table1' will be 'MonthYear[MonthYear]' and there won't be any expanded column in table 'MonthYear'. Hence, there will be no column of 'Dates' table in 'Table1'.

So why filtering 'Dates' table filters 'Table1'? I think the question still persists.

[AlB · Accepted solution]

The expanded table of Dates includes MonthYear. If you refer to the whole table dates you are referring to the expanded table and if you filter you will be filtering the MonthYear table. Then the filter will propagate from MonthYear to Table1 as usual. If you use a column of Dates, you are filtering only that column and not the expanded table. In that case MonthYear won't be affected and neither will Table1  

https://www.sqlbi.com/articles/context-transition-and-expanded-tables/

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers