Forum Discussion
Helper IIAutomatic drill level depending on date filter
Hi,
I have a simple line graph for sales by date (using the full_date hierarchy with Year, Month, Day) and a date slicer.
I have the report saved to default one level level down so the graph loads showing sales over year/month.
If a user selects the last 2 months, it looks silly....
Ideally, I would like the graph to automatically expand one more level (year/month/day) if the user selects a date range that is less than 90 days.
Can this be accomplished in Power BI?
Thanks! 🙂 Joy
I think I may have figured out a way to switch the X axis to show Month if > 90 days in the range, or Week if <= 90 days in the range).
I have 2 date hierarchy columns in dim_date: full_date and calendar_week_start_date.
I have a measure "Day Count" to count the dim_date rows.
I have a calc column that returns "Week" if Day Count <= 90, otherwise "Month"
Then I have a "Date Selection" column:
Date Selection = SWITCH(dim_date[Week or Month],"Week", [calendar_week_begin_date].[Date],"Month",[full_date].[Date])I replaced the Axis value to "Date Selection" and it works!!
2 Replies
- parry2k
Super User
joyhackett it will not auto drill down based on the selection, the user has to manually drill down to the next level.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
joyhackettI think I may have figured out a way to switch the X axis to show Month if > 90 days in the range, or Week if <= 90 days in the range).
I have 2 date hierarchy columns in dim_date: full_date and calendar_week_start_date.
I have a measure "Day Count" to count the dim_date rows.
I have a calc column that returns "Week" if Day Count <= 90, otherwise "Month"
Then I have a "Date Selection" column:
Date Selection = SWITCH(dim_date[Week or Month],"Week", [calendar_week_begin_date].[Date],"Month",[full_date].[Date])I replaced the Axis value to "Date Selection" and it works!!
