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

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!


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.


Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.