Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a few tables in Power BI that have date columns, I want to use 1 filter to control all of them in one report so I created a date table and created the relationships. However now when I filter by date using the new date table, the reports show up blank and I'm not sure why. I have done this a few times and never had an issue.
Anyone have any idea what the issue is here?
The first screen shot I'm using the date from the main table (Av_shift) to filter, which works fine. The second I'm trying to filter using the new date table, but the report just goes blank.
I also added some screen shots of the relationship and data showing that the data type is set to Date/Time.
Here is the Dax for the Date table:
let CreateDateTable = (StartDate as date, EndDate as date, FiscalYearStartDate 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]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each Number.ToText([Year]) & " " & "Q" & Number.ToText([QuarterOfYear])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])+1),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertFiscalMonth = Table.AddColumn(InsertWeekEnding, "FiscalMonth",
each ( if Date.Month([Date]) >= Date.Month(FiscalYearStartDate) then Date.Month([Date]) - Date.Month(FiscalYearStartDate) + 1
else 12 - Date.Month(FiscalYearStartDate) + Date.Month([Date]) + 1 ), type number),
InsertFiscalQuarter = Table.AddColumn(InsertFiscalMonth, "FiscalQuarter", each Number.ToText([FiscalYear]) & " FQ" & Number.ToText([FiscalCalendarQuarter])),
InsertFiscalCalendarQuarter = Table.AddColumn(InsertFiscalQuarter, "FiscalCalendarQuarter",
each ( if Date.QuarterOfYear([Date]) >= Date.QuarterOfYear(FiscalYearStartDate) then Date.QuarterOfYear([Date]) - Date.QuarterOfYear(FiscalYearStartDate) + 1
else 4 - Date.QuarterOfYear(FiscalYearStartDate) + Date.QuarterOfYear([Date]) + 1)),
InsertFiscalYear = Table.AddColumn(InsertFiscalCalendarQuarter, "FiscalYear", each (if Date.Month([Date]) < Date.Month(FiscalYearStartDate) then Date.Year([Date]) else Date.Year([Date])+1), type number)
in
InsertFiscalYear,
#"Invoked FunctionCreateDateTable" = CreateDateTable(#date(2018, 12, 8), #date(2020, 12, 31), #date(2018, 1, 1), null)
in
#"Invoked FunctionCreateDateTable"
Solved! Go to Solution.
I have found that the date column in the date table and in the table it has a relationship with, both need to be of type date. In the query editor, try changing the type for startTime to date. If you need startTime for be of datetime for other reasons, duplicate the column, set its type to date, and use that in the relationship with the date table
Good luck
I have found that the date column in the date table and in the table it has a relationship with, both need to be of type date. In the query editor, try changing the type for startTime to date. If you need startTime for be of datetime for other reasons, duplicate the column, set its type to date, and use that in the relationship with the date table
Good luck
Awesome that worked! I added this and applied then it connected.
= Table.TransformColumnTypes(Av_shift_View,{{"startTime", type date}})
Thanks for your help!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
146 | |
106 | |
104 | |
89 | |
65 |