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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
cheid1977
Helper I
Helper I

If Statement with two different data types

In the data set I have a column for delivery date. If there is no delivery date the source of the data enters "1/1/1900". I am trying to create a second column that leaves the cells blank if this date exists. The problem I am having is that it won't except IF statements referencing two different data types (date and text).  Is there away around using two different data types in an IF statement?  The forumla is below. Thanks in advance for your help.

 

 

 

 

DeliveryDate = if('Total Trans'[Delivery Date]="1/1/1900","",'Total Trans'[Delivery Date])

1 ACCEPTED SOLUTION

I found that the solution is to go under "OPTIONS">Data Load, and uncheck "Automatically detect column types and headers for unstructured sources".  This automatically converts everything to text and allows you to select the data type for each column.

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

What if I am trying to change a calendar date to a fiscal year date, e.g. Oct 1, 2021 is the first month of 2022.  Using the following formula, I get expresstions that yield variant data type cannot be used to dafine calculated columns.  

FiscalYear =
If(
CustDt[Fiscal Month Sort] <4,
right(year(CustDt[Date]) + 1),
CustDt[Date]

Hi,

Try these calculated column formulas in the Calendar Table

Month number = month(calendar[date])

Fiscal Year = if(calendar[month number]>10,year(calendar[date])+1,year(calendar[date]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you very much, Ashish.  I appreciate your help.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
cheid1977
Helper I
Helper I

Thanks for the tip. I am fairly new to this and getting adjusted to the nuances of this tool.

 

KHorseman
Community Champion
Community Champion

The easiest option would be to use the Replace Values button in the Query editor instead of creating a new column. Replace 1/1/1900 with null. Literally type the word null into the box.

 

Anyway if you want to do this as a new DAX column, you need to use DAX rules. You've written an Excel formula. By putting the date in quotation marks and using "" to denote a blank return value, you're treating treating those two things as text rather than dates. You need to use the BLANK() and DATE() formulas.

 

DeliveryDate = IF(
	'Total Trans'[Delivery Date] = DATE(1900, 1, 1),
	BLANK(),
	'Total Trans'[Delivery Date]
)

 





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

Proud to be a Super User!




Sean
Community Champion
Community Champion

Just a reminder although it DOES NOT apply to this question since this is only a comparison NOT a date calculation

 

the first officially supported date in DAX is March 1, 1900 (for date calculations)

I found that the solution is to go under "OPTIONS">Data Load, and uncheck "Automatically detect column types and headers for unstructured sources".  This automatically converts everything to text and allows you to select the data type for each column.

Vvelarde
Community Champion
Community Champion

hi @cheid1977

 

Try with

 

DeliveryDate = if('Total Trans'[Delivery Date]="1/1/1900",blank(),'Total Trans'[Delivery Date])




Lima - Peru

Thanks for responding. Now I get the below error message.

 

A single value for column 'Delivery Date' in table 'Total Trans' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

@cheid1977

 

Are you creating a measure or a calculated column?

 

This is with a calculated column

Column.png




Lima - Peru

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.