cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 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!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

@Cramos

 

Try using this Dax:

 

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

 

 

Dates.png

 

 




Lima - Peru

View solution in original post

10 REPLIES 10
Highlighted
Community Champion
Community Champion

@Cramos

 

You can use Blank().

 

Let me know if works in your problem.




Lima - Peru
Highlighted

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

 

IF(ColumnDate=Blank()...?

 

Thanks

Highlighted
Community Champion
Community Champion

@Cramos

 

Try using this Dax:

 

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

 

 

Dates.png

 

 




Lima - Peru

View solution in original post

Highlighted

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Thanks, it works perfectly! 

Highlighted

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

Highlighted

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?

Highlighted
New Member

 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

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors