Replace Blank values with String in Conditional Check: DAX

0
votes

I'm trying to execute a conditional check on a column containing dates & blanks in some cells. Have been trying multiple functions to convert blanks into "NA" but haven't been able to do so. I read in MS doc that blanks can only be replaced by numeric values, not string. Not sure if there's a workaround: Below my code:

Total Number of Days = IF(ISBLANK([Start Date]),BLANK(),TODAY() - [Start Date])

Above code works fine & cells with no values are shown as blank. But I tried below code:

Total Number of Days = IF(ISBLANK([Start Date]),"NA",TODAY() - [Start Date])

This doesn't work. I even tried to SUBSTITUTE the column value in the same, no luck:

Total Number of Days = IF(ISBLANK([Start Date]),SUBSTITUTE([Start Date],"","NA"),TODAY() - [Start Date])

REPLACE also doesn't do any good here. Any ideas?

1
powerbidax
Data in each column must be a consistent data type in Power BI data model. You cannot do this for a calculated column because TODAY() - [Start Date] returns Numeric values while "NA" is a String. – Kosuke Sakai
I tried creating another column on top of [Start Date] and using SWITCH for the solution, does SWITCH also not work this way? Start Date Modified= SWITCH(TRUE(),[Start Date] = BLANK(),"NA", [Start Date] <> BLANK(), [Start Date]) This is putting "NA" for BLANK values, but the other condition doesn't work. Any ideas? – knowone
This neither will work, because the problem is you are trying to put values of different types in one column. It would be possible once you convert all values to String, for example Total Number of Days = IF ( ISBLANK ( [Start Date] ), "NA", FORMAT ( TODAY ( ) - [Start Date], "0" ) ). But in that case, you can no more make any numeric calculation (e.g. SUM, AVERAGE) on Total Number of Days, so it depends on the use case. – Kosuke Sakai

1 Answers

0
votes

After some tries, got the answer. In SWITCH I believe now:

Start Date Modified = SWITCH(TRUE(),[Start Date] = BLANK(),"NA", [Start Date] <> BLANK(), FORMAT([Start Date],"DD/MM/YYYY"))