Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cramos
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
Vvelarde
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

11 REPLIES 11
harshadshettiwa
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

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

Vvelarde
Community Champion
Community Champion

@Cramos

 

You can use Blank().

 

Let me know if works in your problem.




Lima - Peru

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

@Vvelarde  You're a lifesaver. Thanks!

Apoligies,

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?

Thanks, it works perfectly! 

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!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.