topic Re: Having two tables reference off of each other, one column for each table that will do that in DAX Commands and Tips

Having two tables reference off of each other, one column for each table that will do that

Anonymous — Tue, 24 May 2022 16:46:58 GMT

I'm trying to create a projected deadlines column but each tasks date is dependent on the previous task and there are set # of days itll take for each task to be completed. I was able to create a calculated deadline utilizing the logic above but it cannot go farther than the last completed deadline so I was looking to have my new projected deadline (to show when the rest of the project could be completed if everything goes well) reference itself in a column but thats giving me a 'circular dependency'. So I wanted to know if I would have the same problem if I were to do it in separate tables and have a one column in each to reference each other instead (technically still doing the same work just in another table and having it reference off of different table instead)

Re: Having two tables reference off of each other, one column for each table that will do that

tamerj1 — Tue, 24 May 2022 17:07:52 GMT

Hi Anonymous

This is a complex subject as dax does not support recursive calculations. Nevertheless, this is sometimes possible ising some mathematics. Usually it is expensive in terms of performance. Please share more details and I'll see if anything can be done.

Re: Having two tables reference off of each other, one column for each table that will do that

Anonymous — Tue, 24 May 2022 19:04:42 GMT

So realistically, each task can only be completed if the prior ones before it were completed as well. The order is different on what task is dependent on another so I have it set where I made them unique identifiers using an ID. The Calculated deadline is this DAX code -

Calculated Deadline = IF ( NOT(ISBLANK('RFP_Milestone (Calc)'[Dependent Completed Dates])|| 'RFP_Milestone (Calc)'[Dependent Completed Dates] = ""),DATEVALUE('RFP_Milestone (Calc)'[Dependent Completed Dates]) + VALUE('RFP_Milestone (Calc)'[# of Days]))

Which brings in the correct deadlines meant to be followed as we complete each tasks but now I want do a projected deadline but I'm stuck on having it reference the calculated deadlines which dont have all of the dates filled in.

Projected deadline DAX Code is -

Projected Deadline = IF(ISBLANK('RFP_Milestone (Calc)'[completedon].[Date]), IF (NOT(ISBLANK('RFP_Milestone (Calc)'[Dependent Completed Dates])|| 'RFP_Milestone (Calc)'[Dependent Completed Dates] = ""),VALUE('RFP_Milestone (Calc)'[Dependent Completed Dates] + VALUE('RFP_Milestone (Calc)'[# of Days])), VALUE('RFP_Milestone (Calc)'[Calculated Dependent Deadline] + VALUE('RFP_Milestone (Calc)'[# of Days]))), 'RFP_Milestone (Calc)'[completedon].[Date])

And the calculated depedent deadline is just a LookupValue that pulls the dependent dates.

And this is where I was thinking I can have it reference itself somehow because as it calculates the new deadline it can then reference itself as a dependent date as well to then create another deadline afterwards. I hope I'm making sense.

Re: Having two tables reference off of each other, one column for each table that will do that

tamerj1 — Wed, 25 May 2022 08:09:18 GMT

Anonymous
Can you please paste some sample data to work with?