Forum Discussion

joyhackett's avatar
joyhackett
Icon for Helper II rankHelper II
5 years ago
Solved

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. I have the report saved to default one level level down so the graph ...
Need Help
  • joyhackett's avatar
    joyhackett
    5 years ago

    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!!
parry2k's avatar
parry2k
Icon for Super User rankSuper User
5 years ago

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