Strange behaviour when using FILTER to filter a different table with no direct relationship?

Question

I have two facts tables, First and Second, and two dimension tables, dimTime and dimColour.

Fact table First looks like this:

and facet table Second looks like this:

Both dim-tables have 1:* relationships to both fact tables and the filtering is one-directional (from dim to fact), like this:

 dimColour[Color] 1 -> * First[Colour]
 dimColour[Color] 1 -> * Second[Colour]
 dimTime[Time] 1 -> * First[Time]
 dimTime[Time] 1 -> * Second[Time_]

Adding the following measure, I would expect the FILTER-functuion not to have any affect on the calculation, since Second does not filter First, right?

Test_Alone = 
CALCULATE (
    SUM ( First[Amount] );
    First[Alone] = "Y";
    FILTER(
        'Second';
        'Second'[Colour]="Red"
    )
)

So this should evaluate to 7, since only two rows in First have [Alone] = "Y" with values 1 and 6 and that there is no direct relationship between First and Second. However, this evaluates to 6. If I remove the FILTER-function argument in the calculate, it evaluates to 7.

There are thre additional measures in the pbix-file attached which show the same type of behaviour.

How is filtering one fact table which has no direct relationship to a second fact table affecting the calculation done on the second table?

Ziped Power BI-file: PowerBIFileDownload

Mankarse Mankarse · Accepted Answer · 2019-11-29T08:30:18

Evaluating the table reference 'Second' produces a table that includes the columns in both the Second table, as well as those in all the (transitive) parents of the Second table.

In this case, this is a table with all of the columns in dimColour, dimTime, Second.

You can't see this if you just run:

evaluate 'Second'

as when 'evaluate' returns the results to the user, these "Parent Table" (or "Related") columns are not included.

Even so, these columns are certainly present. When a table is converted to a row context, these related columns become available via RELATED. See the following queries:

evaluate FILTER('Second', ISBLANK(RELATED(dimColour[Color])))

evaluate 'Second' order by RELATED(dimTime[Hour])

Similarly, when arguments to CALCULATE are used to update the filter context, these hidden "Related" columns are not ignored; hence, they can end up filtering First, in your example. You can see this, by using a function that strips the related columns, such as INTERSECT:

Test_ActuallyAlone = CALCULATE (
    SUM ( First[Amount] ),
    First[Alone] = "Y",
    //This filter now does nothing, as none of the columns in Second
    //have an impact on 'SUM ( First[Amount] )'; and the related columns
    //are removed by the INTERSECT.
    FILTER(
        INTERSECT('Second', 'Second')
        'Second'[Colour]="Red"
    )
)

(See these resources that describe the "Expanded Table" (this is an alternative but equivalent explanation of this behaviour)

https://www.sqlbi.com/articles/expanded-tables-in-dax/

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

)

Strange behaviour when using FILTER to filter a different table with no direct relationship?

1 Answers