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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.