Forum Discussion

iLikeAzureSQL's avatar
iLikeAzureSQL
Helper I
10 years ago
Solved

Date Domension with DirectQuery connection

Hello all, I need to create a Date Dimension table in my model while I'm usting DirectQuery to Azure DB. Creating a new table is disabled when I use DirectQuery. What are my options?   Thanks, i...
Need Help
  • iLikeAzureSQL's avatar
    iLikeAzureSQL
    10 years ago

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

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

iLikeAzureSQL Go to Query Editor and click Enter Data which will let you create a new table.

 

 

 

 

 

 

 

 

 

 

 

iLikeAzureSQL's avatar
iLikeAzureSQL
Helper I
10 years ago

Thank you for your reply, but using that will only give me option to manulay enter the data. I intend to use DAX to create the DATE Dioomension table

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    iLikeAzureSQL

     

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

     

     

    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's avatar
      iLikeAzureSQL
      Helper I
      10 years ago

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

      • elliotdixon's avatar
        elliotdixon
        Responsive Resident
        10 years ago

        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.