- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try using this Dax:
NewDate = IF ( Table1[Date1] = BLANK (), IF ( Table1[Date2] = BLANK (), Table1[Date3], Table1[Date2] ); Table1[Date1] )
Lima - Peru
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try creating a new custom column instead of a new measure. You may need to switch from DirectQuery to do this however.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I get "Expressions that yield variant data-type cannot be used to define calculated columns", not sure how to formulate,
IF(ColumnDate=Blank()...?
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try using this Dax:
NewDate = IF ( Table1[Date1] = BLANK (), IF ( Table1[Date2] = BLANK (), Table1[Date3], Table1[Date2] ); Table1[Date1] )
Lima - Peru
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apoligies,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, it works perfectly!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, you're right, my mistake was that one of the table was store as Text.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-17-2024 09:45 PM | |||
08-07-2023 05:58 PM | |||
08-20-2024 06:48 PM | |||
03-21-2024 09:54 AM | |||
01-08-2024 07:42 AM |
User | Count |
---|---|
101 | |
74 | |
43 | |
38 | |
31 |
User | Count |
---|---|
166 | |
90 | |
65 | |
46 | |
43 |