Forum Discussion
Report Filters
I am making a report with 5 pages, each page uses a different database view. 3 of them have a filed called ReportPeriod which is a date. I would like to add a report filter so that when a date is entered once, it will apply to the other pages too. But Power BI will not let me make this relationship, because this field is not unique. Is there a way to do this?
Hello Lorenz33
You would need a Date table that you could link into all three data tables. Then you apply the filtering to the date table. The DAX expression below will generate a basic date table covering all the dates in your model.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "MonthNum", MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm-yyyy"), "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ), "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) ) )
3 Replies
Hello Lorenz33
You would need a Date table that you could link into all three data tables. Then you apply the filtering to the date table. The DAX expression below will generate a basic date table covering all the dates in your model.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "MonthNum", MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm-yyyy"), "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ), "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) ) )I have a view that has all of the unique dates. But I cannot make the relationship since it is not considered unique. Will this method make what Power BI considers to be unique?
It will, yes. Each date row in the date table is unique.
