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

Date hierarchy drill down across years does not work

Hi,

 

There is a date column in the table and the visual presents number of items per date (year / quarter / month )

To be able to drill down from year to quarter to month, I'm using Date hierarchy in the X axis. And somehow it dumps all the January data, from all years, on to the same January, all Feb data to the February etc. So every month appears once on the X axis.

 

When switching away from the hierarchy, all dates are lined up nicely, but then there is no drilling down.

I can still use the filters by time periods and I can add slicers, but this is less convenient than using the date hierarchy.

 

In fact I'm not sure what is the point of the date hierarchy if there is no way to have the data from multiple periods.

 

Any suggestion?

Thank you.

1 ACCEPTED SOLUTION
timg
Solution Specialist
Solution Specialist

Hi InnaS,

Are you drillingdown to the next level or are you expanding the next level. When you drill down you will see all data aggregated over the available categories (e.g. in this case all january's over all years). If you use the expand drill down option you will categorize the data over the first level + the second level (e.g. january 2020, january 2021, etc.). You can find some more info about the different ways of drilldown here: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill.

Regards,

Tim

View solution in original post

4 REPLIES 4
InnaS
Frequent Visitor

Hi Tim,

now sorting has an option of Year Quarter Month, that is good. Previously it was not available.

I also found turning 'Concatenate Labels' helps to make readable presentation.

The most important was to use 'Expand all down one level in the hierarchy' and not to use the drill mode.

 

Thank you

InnaS
Frequent Visitor

Hi Tim, I've tried both drill down and expand. Rigth now I have this. When I go up one level to Quaters, they are presented ok: Q1 2020, Q2 2020, Q3 2020, Q4 2020, Q1 2021. What am I doing wrong here?

 

drilldown.jpg

timg
Solution Specialist
Solution Specialist

Hi InnaS,

I think this is due to sorting settings. Could you press the elipses on the top right of your visual (the '...' icon) and check if the sorting is set to ascending and by date hierarchy?

Best regards,

Tim

timg
Solution Specialist
Solution Specialist

Hi InnaS,

Are you drillingdown to the next level or are you expanding the next level. When you drill down you will see all data aggregated over the available categories (e.g. in this case all january's over all years). If you use the expand drill down option you will categorize the data over the first level + the second level (e.g. january 2020, january 2021, etc.). You can find some more info about the different ways of drilldown here: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill.

Regards,

Tim

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors