DAX to get value from another related table based on date filters

[coder_andy]

Hi, I am a newbie to DAX.

I have 4 tables in PowerBI, say T1, T2, T3, T4 with the following relationship:

T1 (*) ---> (1) T2 (1) ---> (*) T3 (*) ---> (1) T4

where * indicates many,

I need to create a DAX to add a calculated column in T1, to lookup a field from T4 based on filitered dates from T3, get latest if there are multiple matches.

What would the DAX look like?

Following is what I tried but always gives me blanks:

CalculatedField = 
CALCULATE( MAX( T4[FieldName] ), 
    FILTER( T3, 
        T3[StartDate] <= MIN(T1[StartDate]) 
        && (T3[EndDate] > MAX(T1[EndDate]) 
    )
)

[Kerrymr · Accepted solution]

Hi Coder_Andy,

I had similar issues in some of my previous projects.

I found that snowflake schemas make for very complicated in Dax (IMO).

Can you convert this to a star schema?

Kerry

[coder_andy]

I ended up simplifying the data model and converting it to a snowflake schema with 2 levels. 

[Kerrymr]

Hi Coder_andy,

I'm glad to hear you got it worked out. 

Modeling is important and a good model can save a lot of dax time and frustration.

Kerry

[v-frfei-msft]

Hi @coder_andy ,

RELATED and RELATEDTABLE should be used here. So you can try to update your formula like this.

CalculatedField = 
CALCULATE( MAX( T4[FieldName] ), 
    FILTER(RELATEDTABLE( T3), 
        T3[StartDate] <= MIN(RELATED(T1[StartDate])) 
        && (T3[EndDate] > MAX(RELATED(T1[EndDate])) 
    )
)

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[coder_andy]

v-frfei-msft

That didn't work. It throws an error that MIN can only accept field names. I tried changing it to MINX, but did not work. 

Since I am adding the calculated field on T1, do I still need to specify RELATED for T1? If I do not write RELATEDTABLE for T3, will the filter include all records from T3, ignoring the relationship?

Unfortunately, I cannot share the data as it is confidential.