Forum Discussion

Frequent Visitor

9 years ago

Solved

IF function when Date is empty

Hi, I have 3 columns with different dates. I need a function that: IF Column Date1 is empty, take Column Date2, and if this one is empty, take Column Date3. If i use Column(Date1)="", it...

9 years ago

Try using this Dax:

NewDate =
IF (
    Table1[Date1] = BLANK (),
    IF ( Table1[Date2] = BLANK (), Table1[Date3], Table1[Date2] );
    Table1[Date1]
)

Community Champion

9 years ago

You can use Blank().

Let me know if works in your problem.

Frequent Visitor

9 years ago

I get "Expressions that yield variant data-type cannot be used to define calculated columns", not sure how to formulate,

IF(ColumnDate=Blank()...?

Thanks

Vvelarde
Community Champion
9 years ago
Cramos

Try using this Dax:

NewDate = IF ( Table1[Date1] = BLANK (), IF ( Table1[Date2] = BLANK (), Table1[Date3], Table1[Date2] ); Table1[Date1] )
- Anonymous
  Not applicable
  9 years ago
  That error message means that the formula you wrote would return two different data types. You've done something like
  
  NewColumn = IF(TableName[ColumnName] = BLANK(), FALSE, TableName[DateColumn])
  
  In that example, one condition would return a true/false value and the other would return a date. A single column can't contain both. It has to be either true/false or a date. I have no idea what you've actually written but it's something like that where the two return values are different types. Your two return values both have to be the same data type.
  - Cramos
    Frequent Visitor
    9 years ago
    Thanks, you're right, my mistake was that one of the table was store as Text.
- MAkiva1
  Helper I
  8 years ago
  I tried using your DAX to create this and received an error "A single value for column 'Date' in table '[Table]' cannot be determined...
  
  I tried using it as a new Column and as a new Measure. Any suggestions?
- Cramos
  Frequent Visitor
  9 years ago
  Thanks, it works perfectly!