0 Kudos

Replacing a value in direct query mode

Status: Delivered
by KWelsh8144 Frequent Visitor on ‎02-08-2019 12:32 PM

Hi, is it possible to add a column and add dax that would include something like.. if text.contains([date], "1800" then "" else ([date])? The problem is there is a default date in the view I'm using and we are unable to update the view or build a new one at this time. The default date replaces anything that has a null value.  I am trying to make the default date show as null, but I'm not sure how to do that in direct query mode. Is this possible?

Status: Delivered
Comments
by Moderator v-yuezhe-msft
Monday

@KWelsh8144,

Please use the DAX as below.

Column = IF(FIND("1800",'Table'[Date],1,0)>0,"",'Table'[Date])



Regards,
Lydia

by Moderator v-yuezhe-msft
Monday
Status changed to: Delivered
 
by KWelsh8144 Frequent Visitor
Tuesday

@v-yuezhe-msft Thanks - however that turn is returning an error that stats "Expressions that yield variant data-type cannot be used to define calculated columns." How can I fix that? The date that I'm trying to get rid of is actually 12/30/1899. So I tried with "1899" and also "12/30/1899" but neither are working. I'm not sure why it's throwing the error.

by Moderator v-yuezhe-msft
Wednesday

@KWelsh8144,

Make sure the data type of your field is text rather than date.

Regards,
Lydia

Idea Statuses