Dynamic trend granularity (30 min / hour / day / week / month) in Power BI

[manoj_0911]

I am building a contact center report where users need to switch the trend granularity dynamically between:

30 minutes
hour
day
week
month
quarter
year

The dataset is based on call center interval data (30 min buckets).

I currently use a Date table and field parameters, but I'm looking for the best modelling approach to support:

• dynamic axis switching
• drilldown hierarchy
• good performance

How have others implemented this in enterprise Power BI models?

[Kedar_Pande · Accepted solution]

@manoj_0911 

Create disconnected Granularity table: { "30min", "Hour", "Day", "Week", "Month", "Qtr", "Year" }

Granularity Rank table linking to your 30min intervals:

Granularity Sort = 
SWITCH(
SELECTEDVALUE(Granularity[Granularity], "Day"),
"30min", 'Intervals'[Interval],
"Hour", HOUR('Intervals'[Interval]),
"Day", DAY('Intervals'[Interval]),
"Week", WEEKNUM('Intervals'[Interval]),
"Month", MONTH('Intervals'[Interval]),
"Qtr", QUARTER('Intervals'[Interval]),
"Year", YEAR('Intervals'[Interval])
)

Slicer on Granularity table → Line chart X-axis = Granularity Sort, Y-axis = your measure. Field parameters for drilldown if needed

[danextian]

Hi @manoj_0911 

Field parameters  is the most lightweight of the options. You should still be able to drilldown by using another field in the visual.

[v-menakakota]

Hi @manoj_0911 ,

I would also take a moment to thank @danextian   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the issue has been resolved. If you continue to experience any problems, please reach out here and we will assist you.

Best Regards, 
Community Support Team

[v-menakakota]

Hi @manoj_0911 ,

I hope the issue has been resolved. If you continue to experience any problems, please reach out here and we will assist you.

Best Regards, 
Community Support Team

[cengizhanarslan · Accepted solution]

1) Use two dimensions: Date + Time/Interval

Keep a standard Date dimension and add a small Time / Interval dimension.

2) Use Field Parameters for dynamic axis

Field parameters are currently the cleanest way to switch trend granularity.

Trend Axis =
{
    ("30 Min", NAMEOF('Time'[HalfHour]), 0),
    ("Hour", NAMEOF('Time'[Hour]), 1),
    ("Day", NAMEOF('Date'[Date]), 2),
    ("Week", NAMEOF('Date'[Week]), 3),
    ("Month", NAMEOF('Date'[Month]), 4),
    ("Quarter", NAMEOF('Date'[Quarter]), 5),
    ("Year", NAMEOF('Date'[Year]), 6)
}

3) Performance considerations

If your Fact table is huge:

• Avoid calculated columns in the fact table

• Use aggregations at the visual level rather than storing hourly/daily aggregates

• Ensure your Date and Time dimensions are small and optimized

[Kedar_Pande · Accepted solution]

@manoj_0911 

Create disconnected Granularity table: { "30min", "Hour", "Day", "Week", "Month", "Qtr", "Year" }

Granularity Rank table linking to your 30min intervals:

Granularity Sort = 
SWITCH(
SELECTEDVALUE(Granularity[Granularity], "Day"),
"30min", 'Intervals'[Interval],
"Hour", HOUR('Intervals'[Interval]),
"Day", DAY('Intervals'[Interval]),
"Week", WEEKNUM('Intervals'[Interval]),
"Month", MONTH('Intervals'[Interval]),
"Qtr", QUARTER('Intervals'[Interval]),
"Year", YEAR('Intervals'[Interval])
)

Slicer on Granularity table → Line chart X-axis = Granularity Sort, Y-axis = your measure. Field parameters for drilldown if needed

[grazitti_sapna]

Hi @manoj_0911,

You are already using best approach by using date table and field parameters.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!