need help with a switch function with a null value

[nerdyplayer]

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

[nerdyplayer]

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.

[Rakesh1705 · Accepted solution]

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 snap

If this resolves your issue then please accept the same as your solution.

[v-yilong-msft]

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.

[HotChilli]

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