Forum Discussion

MrAdrien's avatar
MrAdrien
Frequent Visitor
4 years ago
Solved

Complete a date column using another column from another table

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

 

 

from
  • MrAdrien's avatar
    MrAdrien
    4 years ago

    I switched to mono directional and it worked.

    Thanks a lot !

12 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    MrAdrien 

    You may try

    FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    MAXX (
        CALCULATETABLE ( RELATEDTABLE ( VALUES ( Sprints[end dates] ) ) ),
        Sprints[end dates]
    ),
    Issues[due dates]
)
  • MrAdrien's avatar
    MrAdrien
    Frequent Visitor
    4 years ago

    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's avatar
      tamerj1
      Community Champion
      4 years ago

      MrAdrien 
      Try

      FinalDueDate =
IF (
    ISBLANK ( Issues[due dates] ),
    MAXX (
        CALCULATETABLE ( RELATEDTABLE ( Sprints ) ),
        Sprints[end dates]
    ),
    Issues[due dates]
)
      • MrAdrien's avatar
        MrAdrien
        Frequent Visitor
        4 years ago

        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's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi MrAdrien 
    I believe this should work

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