cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cparker4486
Helper III
Helper III

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

Hello,

 

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

4 REPLIES 4
erhodes
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 PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer

 

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.

 

 

Hi, 

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

 

Thanks,


Dan

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.