cancel
Showing results for
Did you mean:
Helper IV

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

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.

Thank you.

2 REPLIES 2
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

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

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!