cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Harsh_Jariwala Frequent Visitor
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 

 

1.PNG

 

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

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  Datanaut

View solution in original post

3 REPLIES 3
Super User
Super User

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

Hi @Harsh_Jariwala 

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  Datanaut

Harsh_Jariwala Frequent Visitor
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
Super User

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  Datanaut

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 48 members 971 guests
Please welcome our newest community members: