cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iLikeAzureSQL Regular Visitor
Regular Visitor

Re: Date Domension with DirectQuery connection

@Baskar Here is what it looks like

 

3.jpg4.jpg

Baskar Super Contributor
Super Contributor

Re: Date Domension with DirectQuery connection

Hi,

 

Its working for me without any error prompt.

 

Do one thing again copy the entire query which i given, delete all the power query in that table and paste the new query and try...

 

Moderator v-sihou-msft
Moderator

Re: Date Domension with DirectQuery connection

@iLikeAzureSQL

 

You can use DAX to generate a calendar table based on CALENDAR() function. Just new a table and input DAX below:

 

Capture232.PNG

 

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” )
)

Reference:
HOW TO CREATE A DATE TABLE IN POWER BI IN 2 SIMPLE STEPS

 

 

Regards,

iLikeAzureSQL Regular Visitor
Regular Visitor

Re: 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" )
)

View solution in original post

Highlighted
elliotdixon Member
Member

Re: Date Domension with DirectQuery connection

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
Direct Query New Table.png

 

In Import

New Table Import Mode.png

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.

 

 

jsmooth47 Frequent Visitor
Frequent Visitor

Re: Date Domension with DirectQuery connection

I am getting the same issue.

 

I cannot add blank query or a new table to my Direct Query!

 

Super User I
Super User I

Re: Date Domension with DirectQuery connection

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.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors