Forum Discussion
Direct Query Date Conversion in DAX - Performance Issues
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
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.
9 Replies
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.
