Complete a date column using another column from another table

[MrAdrien]

Hi Everybody 🙂

I have a bit of a challenge to face right now

I have a table of 'Issues' that contains a column of [due dates] and sometimes value of this columns are null.

It is null because the due date is in fact the end date of the "sprint" (Agile) that the issues is being treated in.

I want that each time a issue due date is null, he will return the end date from the table 'Sprints'

FinalDueDate = IF(ISBLANK(Issues[due dates]), Sprints[end dates] ,Issues[due dates]])) --> this fail to work

Here is the data model

Table 'Issue' link 1 to n to Table 'IssueSprints'

Table 'IssueSprints' n to 1 Table 'Sprints'

Thank you so much for your help

[tamerj1]

Hi @MrAdrien 
I believe this should work

FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    MAXX ( RELATEDTABLE ( IssueSprints ), RELATED ( Sprints[end dates] ) ),
    Issues[due dates]
)

[MrAdrien]

Hi tamerj1,

First, thank you so much to help me. 

I want to create it in the issue table so I guess that fit the code you proposed.

But actually I have error will entering it : I get parameter is not the correct type on the values function.

Also the function RelatedTable get two parameter no? In your proposition there is only one so I tried this.

FinalDueDate =

IF (

ISBLANK ( Issues[ISSUE_DUE_DATE] ),

MAXX (

CALCULATETABLE ( RELATEDTABLE ( VALUES ( Sprints[SPRINT_END_DATE] ),Sprints[SPRINT_END_DATE] ) ),

Sprints[SPRINT_END_DATE]

),

Issues[ISSUE_DUE_DATE]

)

Thanks again for your help

[tamerj1]

@MrAdrien 
Try

FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    MAXX (
        CALCULATETABLE ( RELATEDTABLE ( Sprints ) ),
        Sprints[end dates]
    ),
    Issues[due dates]
)

[MrAdrien]

Thank you so much for your reactivity.

Syntax is valid but unfortunately date from sprints table or not retrieve (I got a blank return).

Is it because there is an intermediate table between Issues and sprints which is SprintIssues?

Here is a picture of the model.

[tamerj1]

@MrAdrien 
Seems you have two directional relationships. Try either

FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    RELATED ( Sprints[end dates] ),
    Issues[due dates]
)

FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    MAXX ( RELATEDTABLE ( Sprints ), Sprints[end dates] ),
    Issues[due dates]
)

[MrAdrien]

 @tamerj1 

First solution seems not accepted

I cannot call this column with the related function, it doesn't seems to allow it.

Second solution have the same result than the initial one 

[tamerj1]

FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    CALCULATE ( MAX ( Sprints[end dates] ) ),
    Issues[due dates]
)

[MrAdrien]

@tamerj1 

Problem of blank result still there 😞

(I have double check that I have data on the sprints table for this column)

Can the problem come from the fact that there is a bidirectionnal relationship?

I do a calculated column not a measure by the way.

[tamerj1]

Yes. Bidirectional relationships cannot be predicted. Can you please share sample file

[MrAdrien · Accepted solution]

I switched to mono directional and it worked.

Thanks a lot !

[tamerj1]

@MrAdrien 

You may try

FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    MAXX (
        CALCULATETABLE ( RELATEDTABLE ( VALUES ( Sprints[end dates] ) ) ),
        Sprints[end dates]
    ),
    Issues[due dates]
)

[tamerj1]

@MrAdrien 

In which table are you creating this column?