cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConorHG Helper I
Helper I

Date Relationship

Hi, 

 

I am struggling with doing a date relationship. 

I have created a calendar table and added several columns. 

 

In both data view and query editor, I have put the dates I am doing the relationship on in date datatype and I can do the relationship.

But I then receive the below error after the relationship. It seems that after doing the join the date field in the facts column is no longer read as a date? 

 

BedDaysCal.PNG

 

This calc works perfectly before I do the relationship.

 

DateColumn.PNG

 

Any help is appreciated. 


Thanks, 
Conor.

1 ACCEPTED SOLUTION

Accepted Solutions
FarhanAhmed1984 Resolver III
Resolver III

Re: Date Relationship

I think since you are creating relationship of disdate and date table using date column that's why you are limiting the capabilities of date data. Therefore if you need to have all the capabilities for date values (hierarchy) then you should create a DateKey (Integer Value) on both tables and then join it on that. After that you will see all the hierarchy for your date values.

View solution in original post

8 REPLIES 8
Highlighted
FarhanAhmed1984 Resolver III
Resolver III

Re: Date Relationship

Looks like disdate is not a Date Column.

 

- Click on field.

- Click on Modelling tab

- See if data type is Date , change it to date if it is not.

.DataType.jpg

 


Thanks

 

ConorHG Helper I
Helper I

Re: Date Relationship

Hi @FarhanAhmed1984

 

All my date columns are in the correct format. 

 

DisDate.png


Thanks, 
Conor.

FarhanAhmed1984 Resolver III
Resolver III

Re: Date Relationship

Try this.

 

No Bed Days = DateDiff(FIRSTDATE('Hipe Data'[AdmDate]),FIRSTDATE('Hipe Data'[DisDate]),DAY)

 

DateDiff takes Scalar as Input, so you need to have 1 value return for each input.


You may use MAX() or MIN() as well instead of FIRSTDATE

ConorHG Helper I
Helper I

Re: Date Relationship

Thanks @FarhanAhmed1984, that does work for the calc but it also works if I just remove the '.[date]' from 'disdate'. 

 

The problem I'm having seems to be more with the disdate column, without a relationship it works as a date column including date hierarchy.

 

But when I use it in a relationship with a calendar table I have created I can no longer use it as a date hierarchy and it no longer works in the calc it previously worked in. 

 

Relationship.png

 

Thanks,

Conor.

FarhanAhmed1984 Resolver III
Resolver III

Re: Date Relationship

If your calculation uses Date from Date table then it wont work because it is many to one relationship between these two fields. there can be multiple discharge dates for same dates.

 

Otherwise relationship created from YourTable to DateTable won't have any affect on calcualtion.

 

What exactly you are getting when you are creating relationship from Date table ?

 

 

 

 

ConorHG Helper I
Helper I

Re: Date Relationship

When I create the relationship I get the error in my initial post but if I delete the relationship, the calc works perfectly as it is in the initial post. 

The calc isn't using any of the fields from the DateTable just columns from the FactsTable. 

 

So I don't understand what the relationship is doing to stop "'Hipe Data'[DisDate].[Date]" from working when it worked before the relationship? 

 

Appreciate your patience!
Conor.

FarhanAhmed1984 Resolver III
Resolver III

Re: Date Relationship

I think since you are creating relationship of disdate and date table using date column that's why you are limiting the capabilities of date data. Therefore if you need to have all the capabilities for date values (hierarchy) then you should create a DateKey (Integer Value) on both tables and then join it on that. After that you will see all the hierarchy for your date values.

View solution in original post

ConorHG Helper I
Helper I

Re: Date Relationship

Thanks @FarhanAhmed1984 think I kind of understand it now. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors