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

[aktripathi2506]

Please consider following scenario:

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

Table 2		Table 1		Table 3
Date	1 <->*	Emp ID	1 <-> *	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.

[CahabaData]

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.

[aktripathi2506]

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.