Forum Discussion

megg8's avatar
megg8
New Member
2 years ago
Solved

IF date is blank, pull date from different table

i have 2 tables with target closure dates. 

 

I'm trying to write a DAX/formula IF statement that will go "if [tableA] target closure date is blank, populate with [tableB] target closure date, else [tableA] target closure date. 

I tried the below, but kept getting "Expressions that yield variant data-type cannot be used to define calculated columns."

true target close date = IF(ISBLANK('TableA'[Target Closure Date]), CALCULATE(MAX(TableB[Target Closure Date])), 'TableA'[Target Closure Date])
 
Not sure what to do
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi megg8 ,

     

    If it's a date field, then just change it from text type to date type and then try the formula as above to see if it works?

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    Is the data type the same? i.e both Table A closure date and Table B closure date are actually date type (and one of them isn't a number / string)... Otherwise, the issue might be that your column name starts with True, which might be a reserved word (i don't know for sure, because i can't find the docs for this)

    • megg8's avatar
      megg8
      New Member
      2 years ago

      that was the problem, one of my dates had a data type of text. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi megg8 ,

         

        If it's a date field, then just change it from text type to date type and then try the formula as above to see if it works?

         

        Best Regards,
        Adamk Kong

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from vicky_ , please allow me to provide another insight:

     

    Hi  megg8 ,

     

    You can try formula like below and create calculated column in tableA:

     

    True Target Close Date =
IF (
    ISBLANK ( 'TableA'[Target Closure Date] ),
    MAXX ( 'TableB', 'TableB'[Target Closure Date] ),
    'TableA'[Target Closure Date]
)

     

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.