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

View solution in original post

3 REPLIES 3
Super User III
Super User III

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

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
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  Datanaut

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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