Showing results for 
Search instead for 
Did you mean: 
Helper III
Helper III

Why is my Dates table relationship not producing the expected results?



I have a date table called Dates and a data table called Expenses. I created a 1:* relationship between Dates.Date and Expenses.Invoice Date.


When I create a stacked bar chart I get one of two results: nothing or a bar labeled (blank).


If I swap out Dates.Date in the Axis for Invoice Date I get a visual. The problem is the loss in fidelity of the chart: drilldown and contiguous display (if I understand that word correctly). E.g. If I have transactions in January and March but not February I won't see a placeholder for February but I want to see a placeholder for February.


When I try to view the records for the (blank) column I get the following error:



Error Message:

We can't display the detailed record table for this data source without a unique key. Create a flat table with all fields to see a breakdown of the measure with identical rows aggregated.



Chart with (blank) column using "Show items with no data"Chart with (blank) column using "Show items with no data"RelationshipRelationship

Advocate I
Advocate I

I am also haveing the same issue. Didn;t have this issue until i upgraded to the newest version of PBI desktop.

@cparker4486 Just a couple quick checks. Is the data type of both columns a date datatype? looks like the format is correct. Also, your screenshot is obviously small, but have you confirmed that you have all dates in the date table to cover all dates in the expenses table? Your snapshot has 2015 in one and 2016 in the other. I would assume this isn't the issue, but I figured I'd ask.

Looking for more Power BI tips, tricks & tools? Check out the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG



Yes, both are data type Date.

Yes, Dates date range is big enough. It goes from Jan 1, 2015 to Dec 31, 2020. Invoice Date goes from July 27, 2015 to today. (I wondered about the display too. I assume it is PBI showing sample data only.)


I found two records that were missing dates but even now that those are populated I'm getting the same result.




I'm getting the same error message. Is there any update on this?




Helpful resources



We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors