cancel
Showing results for
Did you mean:
Frequent Visitor

## Fail to understand the behavior of ALL() Function

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:

Thanks!
1 ACCEPTED SOLUTION

Accepted Solutions Super User III

## Re: Fail to understand the behavior of ALL() Function

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 3 REPLIES 3 Super User III

## Re: Fail to understand the behavior of ALL() Function

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 Frequent Visitor

## Re: Fail to understand the behavior of ALL() Function

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. Super User III

## Re: Fail to understand the behavior of ALL() Function

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 Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products. Top Solution Authors
Top Kudoed Authors
Users online (1,583)