Date table - Why can't I make a relationship?

[cparker4486]

I come across this issue every time I try to do it. I eventually stumble across the answer but each time I start over and try again I get stuck.

The scenario is that I'm trying to create a Date table and establish relationships to other tables. Creating the Date table itself is easy.

DateAccounts = 
ADDCOLUMNS (
CALENDARAUTO(),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

The hard part is in creating the relationship.

I've set the date columns in both tables to exactly the same thing:

• Data Type: Date
• Format: 2001-03-14 (yyyy-MM-dd)

I've created a many-to-one relationship from Accounts to DateAccounts.

Then I make a bar chart and set the Value to "Accounts.Count of ID" and the Axis to DateAccounts.Date but the chart is blank.

If I drill-down down to Quarter, I get a bar called (Blank). Obviously, a relationship has not been made.

This happens to me every time. Why don't the values align? What magic (obvious mistake more likely) am I missing?

If I keep messing with it I'll eventually change the magic bit and it will work. I just wish I could learn what the problem is so I don't have to hit this brick wall each time. This should be straightforward.

[dkay84_PowerBI]

First, see what happens if you change the date field in the axis from "hierarchy" to "date".

Also, try changing the relationship direction from single to both.  It is possible that the relationship you have is flowing from the fact table to the dimension table, so that when you put dimDate as the axis, it is not passing the filter argument to the fact table.

Also, make sure that the data types are the same for both date fields.

[parry2k · Accepted solution]

my recommendation will be to add DateAsInteger field in your data table as well and link with calendar table on DateAsINteger field. let's try this first and see what happens?

[cparker4486]

@parry2k

Yes. This actually works!

There really seems to be some "magic" happening in the background that is making Power BI behave in a non-obvious way.

[parry2k]

Good to hear 🙂 

[Phil_Seamark]

This happens when the data in your Accounts table can't find the corresponding record in your date table.

Is CalendarAuto working?  What are the oldest and newest dates in your calendar table?  

[cparker4486]

> This happens when the data in your Accounts table can't find the corresponding record in your date table.

I know. That's why I made this post.

> Is CalendarAuto working?  What are the oldest and newest dates in your calendar table?

Yes.

[Phil_Seamark]

Also what happens when you drag [Created On] to the axis of your visual?

[cparker4486]

> Also what happens when you drag [Created On] to the axis of your visual?

I get more wacky behavior from Power BI.

1. It automatically identifies itself as a date hierarchy but shows the axis as if it's an integer.
   
2. If I change it to "createdon" I don't even know what it's doing. I expect it to be grouping by createdon per createdon. So two events on the same day should appear as one vertical bar, two units tall. But it doesn't. It looks like it's grouping per minute or something. I can't tell.
   

[Phil_Seamark]

Hi @cparker4486,

Is there any chance you can share a cut down version of your PBIX file?  I'd love to take a look and try and see why the visuals are mis-behaving for you.

Feel free to PM me a link to the file.

Cheers,

Phil

[cparker4486]

Hi Phil,

Thanks for the offer! That shouldn't be a problem. I'll message you some time this week.