Forum Discussion

nerdyplayer's avatar
nerdyplayer
Helper I
2 years ago
Solved

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, ...
DAX
isblank
Null
switch
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.



Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.