Forum Discussion
Date Domension with DirectQuery connection
I had to make minor changes with quotations to have it work:
Date =
ADDCOLUMNS (
CALENDAR ( "1-jan-2000", "31-dec-2025" ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
I am coming up with this problem as well. I need to add a date table to my Direct Query report.
The issue is that the answers so far seem a bit confusing. I can't add a table or use DAX blank query to add the table.
In Direct Query Mode
In Import
The option to add a new table or use a blank query is all possible and I have used before in Import mode however in Direct Query mode it is not avaliable.
Adding any tables or custom columns just prompts me to change the report from Direct Query to Import mode.
Help on this would be greatly appreciated.
Cheers. ED.
I am getting the same issue.
I cannot add blank query or a new table to my Direct Query!
As I understand it, DirectQuery is most appropriate to use when connecting to a source that has a fairly complete data model. Your date dimension should come from your data source just like everything else. If you cannot create a date dimension in your data source or you want to create a custom date dimension within Power BI's query editor you will need to use import instead of DirectQuery.