IF function when Date is empty

[Cramos]

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 says DAX comparison cant compare Date with Text, the same happen when I use 0 or null.

P.D: Maybe this have been answer before, but I couldn't find it. Sorry in advance

Thank you!!

[harshadshettiwa]

 I am facing a problem in the similar lines.

I am trying to evaluate if the date is bank but if function in DAX is not evaluating the date column (that is- the date column is greyed out) is it because I am using DirectQuery???

Is there any work around for this.?

Thanks

Harshad

[MAkiva1]

Try creating a new custom column instead of a new measure.  You may need to switch from DirectQuery to do this however.

[Vvelarde]

@Cramos

You can use Blank().

Let me know if works in your problem.

[Cramos]

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 · Accepted solution]

@Cramos

Try using this Dax:

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

[munchkin666]

@Vvelarde  You're a lifesaver. Thanks!

[Shameen294433]

Apoligies,

[MAkiva1]

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]

Thanks, it works perfectly! 

[KHorseman]

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]

Thanks, you're right, my mistake was that one of the table was store as Text.