Showing results for 
Search instead for 
Did you mean: 
Helper IV
Helper IV

DAX: RELATED Function problem w.r.t. Relationship

Please consider following scenario:


I have three tables and they are related to eachother as shown below.


Table 2 Table 1 Table 3
Date1 <->*Emp ID1 <-> *Date
Emp ID Task ID Task ID


Table 2 ----- 1 to Many -----Table 1 ------- 1 to Many ----- Table 3 


I want to compare the date column from Table 2 with date column from Table 3 to calculate something in Table 1.


As columns are from different table so I was thinking to use RELATED function to put the conditions on columns from different tables.

But here I am getting one Table 2 with the related function, not able to use for Table 3.


Formula which I want to try is this.


   = IF(Table2[date]<Table3[date], DATEDIFF(Table2[date],Table3[date],DAY),1*DATEDIFF(Table3[date],Table2[date],DAY))


Please suggest the reason and possible solution to get it work.


@Greg_Deckler @CheenuSing @KHorseman @v-ljerr-msft @jahida @Vvelarde

Thank you.

Memorable Member
Memorable Member

to me this looks like a typical stair step table design.


T2 (1) to T1 (many) joined by Emp ID

               T1(1) to T3(many) joined by Task ID


An employee has many tasks, and each task has many sub tasks or steps.  Quite common.


I believe if your starting point of reference is T2 then in your formula preceed the T3 Date field with the RELATED function and intellisense will allow you to find/select the T3 Date field.


One of the nice things about Power BI is you don't have to flatten tables - but if it makes things simpler you can merge the T1 Emp ID onto the T3 table.... and then just work with T2 and T3....but not sure this is important as it won't change your formulas.


Helper IV
Helper IV

If we use the relatedtable function, then which function should be use to put reference for only one column.

For example 


to compare the date column of table 2 with date column of table 3.


Table2[date] < function(relatedtable(table3),date)


I am not sure if there is any such function which can provide us only one column which we can compare with.



Helpful resources

PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!