Forum Discussion
crln-blue
Post Patron
Post PatronError: Expressions that yield variant data-type cannot be used to define calculated columns
Hello!
I have the error: Expressions that yield variant data-type cannot be used to define calculated columns. when I'm using the ISBLANK function on a date.
Below is my column formula:
TEst =
IF('data (2)'[Date1].[Date] < 'data (2)'[Date2].[Date], "Not yet Billed",
IF(ISBLANK('data (2)'[Date3].[Date]),
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 1, "Current",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 31, "1 - 30 Days",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 61, "31 - 60 Days",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 91, "61 - 90 Days",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) > 90, "Above 90 Days",0
)
)
)
)
), IF('ar data (2)'[Date3].[Date] > 'ar data (2)'[Date1].[Date],
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 1, "Current",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 31, "1 - 30 Days",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 61, "31 - 60 Days",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 91, "61 - 90 Days",
IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) > 90, "Above 90 Days", 0
)
)
)
)
), "Cleared"
)
)
)
ISBLANK and FORMAT function isn't working. The error exists on the 2nd IF statement where I'm checking if one my date columns is blank.
Any help is appreciated. Thanks!
crln-blue , 0 need to "0", Also try to use Switch - https://www.youtube.com/watch?v=gelJWktlR80
TEst = IF('data (2)'[Date1].[Date] < 'data (2)'[Date2].[Date], "Not yet Billed", IF(ISBLANK('data (2)'[Date3].[Date]), IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 1, "Current", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 31, "1 - 30 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 61, "31 - 60 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 91, "61 - 90 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) > 90, "Above 90 Days","0" ) ) ) ) ), IF('ar data (2)'[Date3].[Date] > 'ar data (2)'[Date1].[Date], IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 1, "Current", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 31, "1 - 30 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 61, "31 - 60 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 91, "61 - 90 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) > 90, "Above 90 Days", "0" ) ) ) ) ), "Cleared" ) ) )
6 Replies
- amitchandak
Super User
crln-blue , 0 need to "0", Also try to use Switch - https://www.youtube.com/watch?v=gelJWktlR80
TEst = IF('data (2)'[Date1].[Date] < 'data (2)'[Date2].[Date], "Not yet Billed", IF(ISBLANK('data (2)'[Date3].[Date]), IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 1, "Current", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 31, "1 - 30 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 61, "31 - 60 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 91, "61 - 90 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) > 90, "Above 90 Days","0" ) ) ) ) ), IF('ar data (2)'[Date3].[Date] > 'ar data (2)'[Date1].[Date], IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 1, "Current", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 31, "1 - 30 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 61, "31 - 60 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) < 91, "61 - 90 Days", IF(DATEDIFF('data (2)'[Date4].[Date],'data (2)'[Date1].[Date],DAY) > 90, "Above 90 Days", "0" ) ) ) ) ), "Cleared" ) ) )
crln-blueHello,
I changed my IF to:
IF('ar data (2)'[Date3].[Date] = "0",but I now have a new error message: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.Yes, will try to use SWITCH CASE statements. Thanks
- v-easonf-msft
Community Support
Hi, crln-blue
Can you show your current code and a screenshot of the error message? Everything seems to be working fine on my side.
Best Regards,
Community Support Team _ Eason - amitchandak
crln-blue . please check I have already correct that in else "0" . isblank is fine
- crln-blue
So I should use SWITCH function first? Error still exists in my end.
- crln-blue
Thanks v-easonf-msft and amitchandak . I overlooked my error. It was the 0 on my if statement. Thanks guys for the help! Will close this thread now.
