Paginated Report Date Parameter Shows Full Dim_Date Range (2020–2030) Instead of Fact Table Range (M

[ahmedshalabyy12]

Hi everyone,

I’m building a Power BI Paginated Report (Report Builder) connected to a semantic model.
I have a fact table fact_table related to Dim_Date (Dim_Date[date] ↔ fact_table[report_day]).

My dataset query works fine:

EVALUATE
SUMMARIZECOLUMNS(
'fact_table'[report_day],
'fact_table'[site_code],

RSCustomDaxFilter(@DimSiteSite,EqualToCondition,[Dim_Site].[Site],String),
RSCustomDaxFilter(@DimDateyear,EqualToCondition,[Dim_Date].[year],String),
RSCustomDaxFilter(@DimDateyearmonth,EqualToCondition,[Dim_Date].[year_month],String),
RSCustomDaxFilter(@DimDateyyyyww,EqualToCondition,[Dim_Date].[yyyy_ww],String),
RSCustomDaxFilter(@DimDatedate,EqualToCondition,[Dim_Date].[date],DateTime),

"count_rows", [count_rows]
)

Issue: The Date parameter dropdown shows the full Dim_Date range (2020–2030), but data in fact_table only exists from May 2025 to Jan 2026.

How can I restrict the Date parameter values to only the dates that exist in the fact table (or min/max fact date range)? 

Thanks!

[v-nmadadi-msft]

Hi @ahmedshalabyy12 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @ahmedshalabyy12 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft]

Hi @ahmedshalabyy12  ,
Thanks for reaching out to the Microsoft Fabric Community forum.

If your data model permits, one effective approach is to adjust the Date table so it aligns exactly with the date range present in the fact table. When the Date table is created using a DAX calculated table, you can modify its DAX logic to dynamically derive the start and end dates from the fact table (for example, using the minimum and maximum fact dates). This ensures the calendar only contains dates that are actually relevant to the data

For example this DAX will generate dates from 1/1/2024 to 12/31/2026, Modify the dates as per your requirement to match your Fact table

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE(2024,1,1), DATE(2026,12,31) ),
    "Year", YEAR ( [Date] ),
    "MonthNo", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" ),
    "Week", WEEKNUM ( [Date], 2 )
)

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[cengizhanarslan · Accepted solution]

In paginated reports, the available values for a parameter come from the dataset you use for that parameter, not from the main dataset query. Right now your parameter dataset is effectively returning values from Dim_Date, so you’ll see the full 2020–2030 calendar. To restrict it to only dates that exist in the fact table, build the parameter available-values dataset from the fact table (or from Dim_Date filtered by fact existence).

Option 1) Available values = DISTINCT fact dates

Create a separate dataset for the Date parameter (e.g. dsAvailableDates) and use:

EVALUATE
DISTINCT ( 'fact_table'[report_day] )
ORDER BY 'fact_table'[report_day]

Then in the parameter settings:

• Available Values → Get values from a query → dsAvailableDates

  • Value field: report_day

  • Label field: report_day

This guarantees the dropdown only shows dates that have rows in the fact.

Option 2) Use Dim_Date but filter to dates that exist in fact (semantic-model-friendly)

If you need Dim_Date fields (formatted label, year/month hierarchy, etc.), return Dim_Date[date] only where there are fact rows:

EVALUATE
FILTER (
    VALUES ( 'Dim_Date'[date] ),
    CALCULATE ( COUNTROWS ( 'fact_table' ) ) > 0
)
ORDER BY 'Dim_Date'[date]

Because of the relationship (Dim_Date[date] ↔ fact_table[report_day]), the CALCULATE(COUNTROWS(fact_table)) is evaluated per date and removes dates with no data.