CALCULATE() with Filter() on many-side table seems to ignore relelationship direction

[Anonymous]

Hi,

I've posted this question before but for some reason it was only applied to the Spanish forum and not the English one (I have checked), so here goes another try - and my apologies if 2 of the same questions should appear.

The original issue

I have come across a case where I could use some help to understand the behavior of the CALCULATE(..., FILTER()) function, where the FILTER() statement is filtering the table on the many-side of a one-to-many relationship and simply returning all available records (IDs in this case).

Consider the following case:

A very simple setup with 2 tables to track the progress of the project, with a table containing the budget hours for each project '(Project Hours Budget') and a table containing the actual reports of the time spent on the projects ('Project hours spent'). In these tables there is a one-way relationship that goes from 'Project Hours Budget' to 'Project Hours Spent'.

In this configuration I would like to create a measure that calculates the sum of the budget hours for the projects that have started (where the actual time spent has been reported).

This (apparently) can be done using the following measure:

Budget Hours Measure =
    CALCULATE(
        SUM('Projects Hours Budget'[Budget Hours]),
        FILTER(
            'Project Hours Spent',
            'Project Hours Spent'[Project ID]
        )
    )

The question:
I wonder why this works in the first place, since there is no relationship that implies that 'Project Hours Spent' should filter 'Project Hours Budget', which is the table we are doing a SUM() on. The relationship indicates that the Budget table can filter the Hours Spent table, but not the other way around. Also, if I remove the relationship between the tables the result changes (to yield the total SUM as I would've guessed it would be in the first place), so there is no question that the relationship is being used implicitly in some way - I just don't know why / how, and I haven't been able to find any source that indicates this behaviour.

Basically, I assume the engine is using the existing relationship to identify related columns and then filter the Budget Hours table using the Project ID's from the Hours Spent table - and in doing so ignoring the existing relationship direction. I just hope someone could confirm this behaviour and hopefully link me to some documentation or alike.

Below is a picture of the results obtained by using the above measure and using a simple SUM(), as well as an overview of the content of each table:

EDIT: based on my comment further down I realised that including the queries from DAX Studio would be ideal, so here they come:

1st query:

DEFINE TABLE '$TTable2' := 
SELECT
	SIMPLEINDEXN ( 'Projects Hours Budget'[Project ID] )
FROM 'Project Hours Spent'
	LEFT OUTER JOIN 'Projects Hours Budget' ON 'Project Hours Spent'[Project ID]='Projects Hours Budget'[Project ID]
WHERE
	 ( PFCASTCOALESCE ( 'Project Hours Spent'[Project ID] AS  INT ) <> 0 ) ;

2nd query:

DEFINE TABLE '$TTable1' := 
SELECT
	SUM ( 'Projects Hours Budget'[Budget Hours] )
FROM 
	'Projects Hours Budget'
WHERE
	'Projects Hours Budget'[Project ID] ININDEX '$TTable2'[$SemijoinProjection];