Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
- 1 table "Dates" I created with calendarauto(12)
Also created a many to one relationship between these columns:
I can create a table view showing the dates from SubprojectsAll
Same for the Dates table (though I wonder why I can't show the date itself and only the dateparts):
Now when I try to create a table view by combining both I get a blank result:
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.
Solved! Go to Solution.
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!
you need to write click on date field and then choose date instead of date hierarchy in values field.
When i try date/time first i get the same error:
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...
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)
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 😛
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
And here also the relationship:
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!
Links see my post above yours 😉
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?
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?
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.
I made a csv from the sql data.
Then imported in power bi.
When I try to change the type of the date column to "Date" I get following error:
Here is a link to the csv:
https://drive.google.com/open?id=13xtd9hGe46ijpXKltOz12SnrurLx5YdH
I got it to work with the csv without any problems..
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.
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)
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!
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:
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |