cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joyhackett
Frequent Visitor

Automatic 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.

joyhackett_0-1627500117745.png

I have the report saved to default one level level down so the graph loads showing sales over year/month.

joyhackett_2-1627500350876.png

If a user selects the last 2 months, it looks silly....

joyhackett_3-1627500394338.png

 

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.

joyhackett_4-1627500449395.png

Can this be accomplished in Power BI?

Thanks! 🙂 Joy

 

1 ACCEPTED SOLUTION
joyhackett
Frequent Visitor

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!!

View solution in original post

2 REPLIES 2
joyhackett
Frequent Visitor

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!!

View solution in original post

parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors