cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
coder_andy Frequent Visitor
Frequent Visitor

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

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]) )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Kerrymr Regular Visitor
Regular Visitor

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

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

 

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

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

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.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
coder_andy Frequent Visitor
Frequent Visitor

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

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. 

Kerrymr Regular Visitor
Regular Visitor

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

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

 

View solution in original post

coder_andy Frequent Visitor
Frequent Visitor

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

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

Kerrymr Regular Visitor
Regular Visitor

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

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors