Report Filters

[Lorenz33]

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?

[jdbuchanan71 · Accepted solution]

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

[Lorenz33]

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?

[jdbuchanan71]

It will, yes.  Each date row in the date table is unique.