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