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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors