Using DATE & ISERROR in DirectQuery - Measure or Column

[michaelmichael]

I'm trying to right DAX to read the dates of a number like so "191002" for "2nd October 2019"

I'm using DirectQuery for my data and some items have letters or incorrect formatting such as "2019" instead of "19".

I'm having problems here, because I can't use ISERROR function in columns, and I can't get DATE to work in measures

Any help would be appreciated

[v-jayw-msft · Accepted solution]

Hi @michaelmichael ,

Based on my test, ISERROR() function and DATE() function works fine in Measure, please make sure you are creating a measure.

Below are the measure i created and the result i get based on my own logic. 

Measure = 
var sv = SELECTEDVALUE(Table_1[date])
var lenth = LEN(sv)
return
IF(lenth=6,DATE(MID(sv,1,2),MID(sv,3,2),MID(sv,5,2)),IF(lenth=8,DATE(MID(sv,1,4),MID(sv,5,2),MID(sv,7,2)),DATE(2099,12,31)))

 

Your logic might be different, so please share some sample data and your expected result if you need further help. Please make sure you don't have any Confidential Information before sharing data.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.