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 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?
Solved! Go to 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" ) ) )
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.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |