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,

iLikeAzureSQL

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

16 Replies

    • 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,

  • Baskar's avatar
    Baskar
    Resident Rockstar
    10 years ago

     

     

    Use the below Power Query to create Date Master then use it, Try it

     

    let
    Source = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
    let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & "'" & Text.Range(Number.ToText([Year]),2,2)),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekStarting = Table.AddColumn(InsertWeekEnding, "WeekStart", each Date.StartOfWeek([Date]), type date)
    in
    InsertWeekStarting,
    #"Invoked FunctionSource" = Source(#date(2016, 1, 1), #date(2016, 12, 31), null),
    #"Changed Type" = Table.TransformColumnTypes(#"Invoked FunctionSource",{{"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayInWeek", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
    #"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Date - Copy", Date.WeekOfYear}}),
    #"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Date - Copy", "WeekofMonth"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"DateInt", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Sorting Order", each [Year]*100 + [MonthOfYear]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each true),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Sorting Order", Int64.Type}})
    in
    #"Changed Type2"

      • Baskar's avatar
        Baskar
        Resident Rockstar
        10 years ago

        1. Click Edit Queries window

        2.  Choose Blank Query in Get Query button and then click ok.

        3. click "Advanced Editor" and totally replace the power which i given.

         

         

        now youe date master ready to use...

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    10 years ago

    A calendar with only dates:

     

    CalendarTable = CALENDAR("01/01/2015";"31/12/2015")