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

[Per-Erik]

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 data-table (date column)The date format for this columnThe error and the date hierarchy configurationIf you turn of the date hierarchy it works but you get 400 000 entriesTHis is how we want it to look like

[Eric_Zhang]

@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.

[Per-Erik · Accepted solution]

Hi Eric Zhang!

It works 

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]

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