Forum Discussion
Post ProdigyCan date fields used as parameter fields have date hierarchy?
I have 2 date-type parameter fields I'm using in this chart - both have their built-in date hierarchies and the x-Axis is set to Categorical. However, as you can see on the chart, the x-Axis is showing each unique date value in the table (excluding the ones not present in the dataset). It is required to make the x-Axis swappable between the two dates. That said, is it possible for both dates used as parameter fields to retain their date hierarchy when plotted in the chart?
Hi olimilo
When using Field Parameters to switch date fields on the axis, Power BI treats the parameter output as a regular column, not as a native date field. Because of that, the built-in date hierarchy and drill-down capability are not preserved, and the axis becomes categorical, which explains why you see every distinct date value instead of a proper time hierarchy.
A more stable and scalable approach is not to switch the axis, but to keep a single continuous date axis (from your Calendar table) and let users switch measures instead of date columns.
To support multiple business date meanings (such as Report Date vs Audit Start Date), you can use the common modeling technique of Role-Playing Dimensions:
Create one Calendar table
Create separate, inactive relationships from Calendar to each date column (e.g., ReportDate, AuditStartDate)
In each measure, activate the appropriate relationship using USERELATIONSHIP()
Use a Measure Field Parameter to let the user choose which date-logic they want
This approach gives you:
Full date hierarchy support (Year > Quarter > Month > Day)
Continuous timeline axis instead of categorical labels
Correct time intelligence behavior
Clean and maintainable semantic model
Example pattern (simplified):
Total Orders (Report Date) =
CALCULATE(
[Total Orders],
USERELATIONSHIP('Calendar'[Date], 'Fact'[ReportDate])
)
Total Orders (Audit Start Date) =
CALCULATE(
[Total Orders],
USERELATIONSHIP('Calendar'[Date], 'Fact'[AuditStartDate])
)more information about the method here:
Apply the measures instead of the time line as a field parametrs.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
1 Reply
- Ritaf1983
Super User
Hi olimilo
When using Field Parameters to switch date fields on the axis, Power BI treats the parameter output as a regular column, not as a native date field. Because of that, the built-in date hierarchy and drill-down capability are not preserved, and the axis becomes categorical, which explains why you see every distinct date value instead of a proper time hierarchy.
A more stable and scalable approach is not to switch the axis, but to keep a single continuous date axis (from your Calendar table) and let users switch measures instead of date columns.
To support multiple business date meanings (such as Report Date vs Audit Start Date), you can use the common modeling technique of Role-Playing Dimensions:
Create one Calendar table
Create separate, inactive relationships from Calendar to each date column (e.g., ReportDate, AuditStartDate)
In each measure, activate the appropriate relationship using USERELATIONSHIP()
Use a Measure Field Parameter to let the user choose which date-logic they want
This approach gives you:
Full date hierarchy support (Year > Quarter > Month > Day)
Continuous timeline axis instead of categorical labels
Correct time intelligence behavior
Clean and maintainable semantic model
Example pattern (simplified):
Total Orders (Report Date) =
CALCULATE(
[Total Orders],
USERELATIONSHIP('Calendar'[Date], 'Fact'[ReportDate])
)
Total Orders (Audit Start Date) =
CALCULATE(
[Total Orders],
USERELATIONSHIP('Calendar'[Date], 'Fact'[AuditStartDate])
)more information about the method here:
Apply the measures instead of the time line as a field parametrs.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly