cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aktripathi2506
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.

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

 

 

 

 

www.CahabaData.com
aktripathi2506
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

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.