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
Per-Erik
Frequent Visitor

Date Hierarchy - couldn't load the data for this visual

Hi

I have a erro that occurred after the release of July Power bi Desktop release. The error still remains unfortunately after the August update of Power bi desktop.

 

I have a number of reports that have worked fine since February in 2016. The only thing I do now is update reports with new data (refresh) and release the reports to my managers (through power bi web-portal).

All report is based on a simple table that contains the data for different cases and the date when the case was submitted to us. No releations in the table.

 

We import the table into power bi desktop (no direct query)

 

The error we get is: "The query referenced column 'Local Date Table_ed8123c6-3ff2-4895-ad8e-43319f6529b9' [Year] Which depends on another column, or measure That relationship is not in a valid state."

 

The error occurs a "slicer" - component used to select the year that we want to see the statistics for.
The error occurs when the date field is set to "Date Hierarchy", which it has always has been before (and worked fine)
When I remove datehierch-configuration we can see all the data but then get we 400,000 entries to select from.

 

I have made som new reports from the data same datasource (sql-server) in without any configurations or formulas. We still get the same error.

 

I have tried reinstalling power bi both on the same computer, but also a brand new computer without power bi if there would be any local files installed, does not help either 😞

 

I have now tested all kinds of configuration but I have not managed to solve this problem. Is there anyone else who has had the same problem and found a solution?


I include some images that describing the error and how the table data looks like-

 

Best regards Per-Erik

The error messageThe error messageThe data-table (date column)The data-table (date column)The date format for this columnThe date format for this columnThe error and the date hierarchy configurationThe error and the date hierarchy configurationIf you turn of the date hierarchy it works but you get 400 000 entriesIf you turn of the date hierarchy it works but you get 400 000 entriesTHis is how we want it to look likeTHis is how we want it to look like

 

1 ACCEPTED SOLUTION

Hi Eric Zhang!

 

It works Smiley Very Happy

What a luck for me that you saw the first line in the data-table I sent to you.
But it is just as you write. It worked before the updates in July month.

 

To me this seems to be a bug that Microsoft must take care of and I hope Microsoft fix this bug in the next release.

In my case I can delete these records in the database because they are not important to the overall statistics.

I will close and accept this solution once we have received feedback from PowerOfLove,  if it is the same reason for the error there.

 

Thanks so much for your help! 

 

Best regards Per-Erik

View solution in original post

3 REPLIES 3
Eric_Zhang
Microsoft Employee
Microsoft Employee

@Per-Erik

 

I see a date "1899-12-30" in your case, based on my test, that is the culprit in your case, if I remove all the dates earlier than 1900-01-01, the error would go away in my test.

 

And indeed, "1899-12-30" worked in earlier release, in my case, I tested it on a Mar 2016 release. However In recent releases, there's something related to the date hierarchy, eg Drill-down (or back up) on line charts in Aug release. so I just guess that the PG team has changed the date hierarchy mechanism it was.

 

If the "1899-12-30" is not your case, please upload the pbix file for further diagnose.

Hi Eric Zhang!

 

It works Smiley Very Happy

What a luck for me that you saw the first line in the data-table I sent to you.
But it is just as you write. It worked before the updates in July month.

 

To me this seems to be a bug that Microsoft must take care of and I hope Microsoft fix this bug in the next release.

In my case I can delete these records in the database because they are not important to the overall statistics.

I will close and accept this solution once we have received feedback from PowerOfLove,  if it is the same reason for the error there.

 

Thanks so much for your help! 

 

Best regards Per-Erik

ThePowerOfLove
Regular Visitor

I have the same problem. Recently sent a frown. Date Hierarchy results in "Can't display the visual" and a complaint that the column depends on another column, relationship, or measure. It is not a calculated column, and the column has no relationships. This error did not occur before the recent update. Moderator asked for some details in the previous thread but never got back. Previous Thread

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.