How to calculate average where filters span two different tables

[miss-ness]

Hello! I'm looking for some guidance on how I can implement this idea:

CALCULATE(AVERAGE('Table1'[Field1]), 

    'Table1'[Field2] = 'Table2'[Field1], // Same city names but from two different tables

    'Table2'[Field2] <= 'Table1'[Field3], // Fiscal date from table2 is less than or equal to fiscal date from table1

    'Table2'[Field3] >= 'Table1'[Field3] // Helper date from table2 is more than or equal to fiscal date from table 1

)

[Anonymous]

Hi @miss-ness ,

It looks like you’re trying to calculate the average of a field from Table1 based on some conditions involving fields from both Table1 and Table2.

I think you can change your DAX code and here is the changed code:

Measure =
CALCULATE (
    AVERAGE ( 'Table1'[Field1] ),
    FILTER (
        'Table1',
        'Table1'[Field2] = RELATED ( 'Table2'[Field1] )
            && 'Table2'[Field2] <= 'Table1'[Field3]
            && 'Table2'[Field3] >= 'Table1'[Field3]
    )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miss-ness]

Hi Yilong, thanks very much for your response, I've modified the DAX expression as you suggested but I am getting an error on each of the three filter conditions: The column[s in table 2] do not have a relationship to any table available in the current context. 

BUT, table2 is in fact connected to table1 in the data model actually by the columns in the first filter expression. Would you have any ideas as to why I'm receiving this error?