Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WimVan
Helper I
Helper I

Relationship problem between date columns from 2 tables

I have 2 tables:

- 1 table "SubprojectsAll", loaded from sql via directquery, contains a date column named "Date last primary proposal revision"  (formatted in Power BI as date)

2019-06-25_12-52-57.png

2019-06-25_13-09-46.png

 

- 1 table "Dates" I created with calendarauto(12)

2.png2019-06-25_13-11-12.png

 

 

Also created a many to one relationship between these columns:

10.png

 

I can create a table view showing the dates from SubprojectsAll

3.png

 

Same for the Dates table (though I wonder why I can't show the date itself and only the dateparts):

4.png

 

Now when I try to create a table view by combining both I get a blank result:

 

6.png

 

For some reason the relation doesn't work (at least appears so to me). I have similar issues creating charts, slicers etc. When ever I use the "Dates" data I get a blank result.

 

Please can you explain what is going wrong.

 

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

And furthermore, it has to be done in the query editor. Does not work if you attempt to do it from the normal modelling pane!


Connect on LinkedIn

View solution in original post

27 REPLIES 27
vishanshul
Frequent Visitor

you need to write click on date field and then choose date instead of date hierarchy in values field.date.PNG

 

When i try date/time first i get the same error:

2019-06-26_14-24-31.png

TeigeGao
Solution Sage
Solution Sage

Hi @WimVan ,

I would suggest you checking the date format of your date column, I found it is strange that the date comes with the 1900 when you are using the CALENDERAUTO() function, actually, this problem often occurs when the year and month are recognized wrong.

If possible, you can share a sample pbix without personal information to us for analysis.

Best Regards,

Teige

Thanks for your reply TeigeGao. Unfortunately I don't see any menu item  to attach a file here in my message editor...

 

2019-06-26_10-48-17.png

tex628
Community Champion
Community Champion

@WimVan 
Swap the 'date hierarchy' to date:
image.png
And if you want to upload a file, you need to do it through another service. Cant be done through the community website.

Br,
J


Connect on LinkedIn

I think the 1900 dates were coming from the fact that one of my tables had a column with dates of that year. 

After I removed those dates the calendarauto generated dates from 2000 onwards.

 

Anyway, I tried your suggestion with 

CALENDAR(DATE(2010,1,1),TODAY())

and also swapped hierarchy to "date".

 

Columns in both tables have data type  "Date" and format "yyyy-MM-dd"

 

Still I get an empty view (see the one to the right)

2019-06-26_11-09-19.png

 

 

tex628
Community Champion
Community Champion

This is seriusly messing with my head..... 

 

Create a new page
Remove any report level filters
Add a table

Add only the date column and the other column it has a relationship with.
Post results

Otherwise, if you can share the file, it would be the easiest way 😛


Connect on LinkedIn

Will have to find a file sharing solution.

 

Meanwhile tex628 I did what you asked:

no report level filters

just showing you the visual filters 

2019-06-26_11-29-32.png

And here also the relationship:

2019-06-26_11-35-11.png

tex628
Community Champion
Community Champion

This is really really weird. If you have an email that is somewhat anonymous you can email me the file, otherwise just give me a heads up when you got the filesharing sorted! 


Connect on LinkedIn

Links see my post above yours 😉

tex628
Community Champion
Community Champion

You are importing using direct query, which means i cant see any of the data in the pbix file! I'm not certain if this could be the cause of the issue as im not too used to direct query myself! If you change the connectivity to import instead, does it work?


Connect on LinkedIn

I had the same idea it could be related to directquery. But with import I have the same problem.

 

By the way the other link I provided you is a file to generate the sql table with data.

You are not familiar with sql?

tex628
Community Champion
Community Champion

I'm pretty terrible when it comes to SQL 🙂 But if you can upload a version with with import connectivity, i can take a look at that one instead. 


Connect on LinkedIn

I made a csv from the sql data.

2019-06-26_13-51-51.png

 

Then imported in power bi.

When I try to change the type of the date column to "Date" I get following error:

2019-06-26_13-51-21.png

tex628
Community Champion
Community Champion

I got it to work with the csv without any problems.. image.png

Both the date columns had to be converted to date format but otherwise i did nothing but import it and create the relationship, very strange indeed.


Connect on LinkedIn

How did you manage to set the "proposal date" column to type "date"?

As I said I get an error and was not able to continue.

 

What I do is clicking the proposal date in the right side pane, then in the top menu change to "Date" in the data type dropdown. However then i got that error (see screenshot in previous post)

tex628
Community Champion
Community Champion

And furthermore, it has to be done in the query editor. Does not work if you attempt to do it from the normal modelling pane!


Connect on LinkedIn

Problem solved!  Also with direct query.

 

The problem indeed was that I didn't to the conversion to date type with the query editor.

So even if in the model it "showed" like Date, actually the dates itself were still date/time.

As a result it couldnt match the date/times of my sql table with the dates of the calendarauto because the date/times in sql contain hours and seconds (like 23:04:05) while the calendar dates have all of them 00:00:00.

 

Below proof of success:

2019-06-26_14-48-21.png

 

I do think it is confusing that you can change type in the model while it's actually having only superficial effect.

 

Thanks a lot guys for helping me out on this, especially tex628 for your patience!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.