Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LeeI
Frequent Visitor

Drill down on Matrix no longer an option after marking date dim table as date table

I have a matrix that I created that used a date dim table date field as one of the values. I wanted to add some previous months calculations to the matrix. In order for my dax to work properly, I marked the date dim table as a date table. Now however, The date field from the date dim table no longer shows a time hierarchy (year-> quarter -> month). it only has stagnant dates (1/1/2023). If you use the date field from the date dim table in a visual, does marking the date dim table as a date table prohibit the drill down feature? After marking the date dim table as a date, I now have this icon next to the field. 

 

LeeI_0-1717005967059.png

 

Has anyone found a way to mark the date dim table as a date table, but still be able to use it in visuals that will allow the drill down feature? 

 

1 ACCEPTED SOLUTION
LeeI
Frequent Visitor

Thanks for the response, @lbendlin. That was my issue. It was not automatically creating a hierarchy when I put a date field on the X axis. I was able to solve this issue for anyone else who might have the same problem when using a self created date dim table. Apparently if you mark a date dim table as a calendar table, it automatically removes the time hierarchy. You can create your own hierarchy for the time within your date dim table. However if your month field in the date dim table is just a text string like "January", it will sort the months alphabetically instead of chronologically. To circumvent this issue, you will also need to add a month field that contains values as a whole number (january would be 1, February 2 etc). You can then sort the text string month field by the whole number month field in the data view, which will allow for the month text strings to be displayed chronologically in your visual. 

View solution in original post

2 REPLIES 2
LeeI
Frequent Visitor

Thanks for the response, @lbendlin. That was my issue. It was not automatically creating a hierarchy when I put a date field on the X axis. I was able to solve this issue for anyone else who might have the same problem when using a self created date dim table. Apparently if you mark a date dim table as a calendar table, it automatically removes the time hierarchy. You can create your own hierarchy for the time within your date dim table. However if your month field in the date dim table is just a text string like "January", it will sort the months alphabetically instead of chronologically. To circumvent this issue, you will also need to add a month field that contains values as a whole number (january would be 1, February 2 etc). You can then sort the text string month field by the whole number month field in the data view, which will allow for the month text strings to be displayed chronologically in your visual. 

lbendlin
Super User
Super User

See it as a good thing. Your "drill down feature"  was adding ungodly amounts of extra calendar tables to your semantic model (one for each date field). That's why you should always disable "Auto Date/Time".

 

You can create your own hierarchies if you want, but most visuals do it for you when you put the date field on the x axis.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors