Forum Discussion
need help with a switch function with a null value
Always having problems with blank value.
i am trying to do a switch function, if the date value is blank "--"
if not, formula will do a datediff between 2 dates.
testing = switch(
TRUE,
ISBLANK(Policy[Final Concurrence Date]),"--",
NETWORKDAYS(Policy[Date_Assigned],Policy[Final Concurrence Date],1)
)
formula is calculating the datediff correctly, but having a hard time if the date is blank/null.
tried IF/else as well, both gave me this error
expressions that yield variant data-type cannot be used to define calculated columns
Those who are smarter than I please help. ty
Hi nerdyplayer
the main issue is confliction of data type within the same column.
Hence to avoid this confliction I have converted the final value in string.
Please check the below snapIf this resolves your issue then please accept the same as your solution.
4 Replies
networkdays returns an integer, the other path returns a text string. You can't have those options in a SWITCH/IF. You have to return the same type from all paths
Hi nerdyplayer ,
I create a table as you mentioned.
When I use your DAX code in a calculated column, it gives me the same error.
The error you’re encountering is due to the fact that calculated columns in DAX cannot return different data types. In your case, the SWITCH function is trying to return both a string (“–”) and a number (result of NETWORKDAYS), which is causing the issue.
If you still want to use calculated column, you can use this DAX code.
Column = SWITCH ( TRUE, ISBLANK ( Policy[Final Concurrence Date] ), 0, NETWORKDAYS ( Policy[Date_Assigned], Policy[Final Concurrence Date], 1 ) )If you want to still use "--", I think you can use Measure, here is the DAX code.
testing = SWITCH ( TRUE, ISBLANK ( MAX('Policy'[Final Concurrence Date]) ), "--", NETWORKDAYS ( MAX('Policy'[Date_Assigned]), MAX('Policy'[Final Concurrence Date]), 1 ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi nerdyplayer
the main issue is confliction of data type within the same column.
Hence to avoid this confliction I have converted the final value in string.
Please check the below snapIf this resolves your issue then please accept the same as your solution.
thank you all. it was indeed the conflicted between the dash. I did need for the dashes for another column and it was useful for me. thank you all again.
