Direct Query Date Conversion in DAX - Performance Issues

[sght16]

As per my company's Security team's risk evaluation, I have to create most of my deployed Power BI apps using Direct Query. My source data contain multiple dates, and are all read in from the source as text in the format, "YYYYMMDD". My M query tricks don't work with Direct Query, and my favorite DAX functions are also not available for Direct Query. DateValue is the only function that I can use of the many other options available in Import based solutions.

However, I'm hitting a major performance issue when I use DAX to define all of my text-dates as date-dates. My current solution (see below DAX) works, it looks good in my visuals, but you can go out for full lunch and a cup of tea before the visuals finish updating. Leadership aren't going to go for that.

I do have loads of other Measures and Columns, but when I can get performance evaluation solutions to run, I get this error, which is why I think it's my dates.

"Data overflow converting to the data type for table <tablename> column <one of my date column names>"

What stupid did I do? Or is this something others experience with Direct Query? 

Landscape info - Again, Text Date is in "YYYYMMDD" format, and this is a HANA view created by my Business Warehouse team.  I develop in Power BI desktop, publish to a premium workspace, deploy apps to users who then use their existing company credentials to access data as per their permissions. The Gateway is solid (built by different team and stress tested), and anyway the performance fails start at the desktop level and go from there for only my needs-to-be-direct-query apps.

Make It A Date =
VAR Year1 =
    LEFT ( [Text Date], 4 )
VAR Mon1 =
    RIGHT ( LEFT ( [Text Date], 6 ), 2 )
VAR Day1 =
    RIGHT ( [Text Date], 2 ) 

// This code uses Swedish format - no idea why as my settings are Eastern Standard USA but it was the only thing that worked
VAR MonthBit =
    CONCATENATE ( "/", Mon1 )
VAR DayBit =
    CONCATENATE ( "/", Day1 )
VAR MonDayBit =
    CONCATENATE ( MonthBit, DayBit )
VAR TextDate =
    CONCATENATE ( Year1, MonDayBit )
VAR FinalDate =
    DATEVALUE ( TextDate )
RETURN
    FinalDate

[tamerj1]

Hi @sght16 

please try

Make It A Date =
VAR Year1 =
VALUE ( LEFT ( [Text Date], 4 ) )
VAR Mon1 =
VALUE ( MID ( [Text Date], 5, 2 ) )
VAR Day1 =
VALUE ( RIGHT ( [Text Date], 2 ) )
RETURN
DATE ( Year1, Mon1, Day1 )

[sght16]

DATE function (DAX) - DAX | Microsoft Learn says for the DATE function, "This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules." and I'm using this in a calculated DAX Column. 

[sght16]

To confirm for other readers, I tried the Value + Date suggestion, and it did not work for a Calculated DAX Column. I'm working on the other suggestion and will update this post with the results when I'm finished.

[tamerj1]

@sght16 

How many rows does the table have?

[sght16]

It's a Direct Query from an ERP, so something in the order of 700K with the hard filtering limits that I placed in M Query. 

[tamerj1]

@sght16 

Perhaps you don't need to create this column. You can locally import or create a date table that contains the text data type date then connect the two tables using this column. 

[sght16]

I'll give this a try for the four most important dates, and see how the performance is effected.

[sght16]

The data cube's current design requires this volume to calculate correctly, so I suspect that the size is too big, in which case there are going to be some unhappy data cube designers when we next meet... We're designing another cube together but that's 3 months off. Fingers crossed that this is within acceptable limits as leadership wants this ASAP... 

[sght16 · Accepted solution]

This date table solution didn't seem to work for performance, but that could have been due to other constructions in my report, so I've decided to split the report into functional sub groups, in this way one report becomes many, which isn't ideal, but the populations are about 1/5 what the total report was originally. I sacrificed scope for run time.