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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

combining data from multiple sources periodically - date hierarchy no longer works

Hi all

 

I'm fairly new to Power Bi and set up an initial table with data from 2020, a seperate date table for time inteligence and seperate shop location table which creates a hiearchy on sales areas.

 

I later imported new data for 2019 from 4 different excel files into one table and added this table (via append) to the main table.

 

The problem I am having is the date drill downs/ hierarchy no longer work on my visualisations.

 

I'm not sure i'm approaching this right or why is has stopped working after the import?

  

1 ACCEPTED SOLUTION

@Anonymous as soon as you have a relationship on date column that hierarchy is not there anymore because internal date tables don't exist anymore, having said that, you can create your own hierarchy in date table and use that and this is the right way to do the model, in other words, you are on right track.

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@Anonymous check the data type of your date column maybe once you append the table the data type of that column is not date anymore and that's why you are seeing the hierarchy.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hello

 

I have checked and all my dates in all tables are formatted as dates. 

amitchandak
Super User
Super User

@Anonymous , Not very clear. But is your calendar covering both 2019 and 2020.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Yes, my date calendar covers all dates 2019 & 2020.

@Anonymous can you share the relationship diagram, if you have marked your date dimension as date table it will not show hierarchy. Provide more detail, how your tables are related, and which date column is not working.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

StoreRegionTable (SalesAreaNo, StoreNo,StoreType, StoreAddress)

MainExport2020Table (ActivityNo, ActivityDate, StoreNo, CustomerCode, ActuvityType, ActivityBrand)

DateTable (Date, Year, Qtr, Month, WeekNo, DayNo)

 

Table above formatted as excel table

Above three tables have a relationship one date in date table to many dates in main

One store in store table to on store in main

Each row in main is one activity and  the aim is to show the number of activities per day , week , month for each store and then a moving 12 week and daily average per store by brand.

 

Imported2019DataTable (ActivityNo, ActivityDate, StoreNo, CustomerCode, ActuvityType, ActivityBrand) 

-

This table has been combined using 4 excel sheets to onetable using get data and then then appended to the current MainExport2020Data table. This import is large.

 

The idea was that any new data will be added to a file, imported and then appeneded to main where the relationships.

@Anonymous as soon as you have a relationship on date column that hierarchy is not there anymore because internal date tables don't exist anymore, having said that, you can create your own hierarchy in date table and use that and this is the right way to do the model, in other words, you are on right track.

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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