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.
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. 

Highlighted
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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,785)