Relationship Issues With DateTime Data Types in Power BI
HTML-код
- Опубликовано: 1 авг 2024
- Learn about a potential pain point when it comes to dealing with DateTime data in a fact table. You can potentially encounter an issue when joining your fact to a calendar table. Specifically if you import a Fact table into your model with a datetime column, and convert that column to a date data type in the model, instead of in Power Query. If you do this then that relationship still won't work until you make that change in Power Query.
RELATED CONTENT 🔗
PQ Vs. DAX Data Types -- endjin.com/blog/2020/03/power...
BECOME A CHANNEL MEMBER 🎉
-- / @havensconsulting
LET'S CONNECT! 🧑🏽🤝🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
VIDEO CHAPTERS 🎥
0:00 - Start of Video
0:40 - Start of Demo
2:55 - Power Query Vs. DAX Data Types Blog
HAVENS CONSULTING PAGES 📄
Home Page - www.havensconsulting.net
Blog -- www.havensconsulting.net/blog-...
Blog Files -- www.havensconsulting.net/blog-...
Files & Templates -- www.havensconsulting.net/files...
Consulting Services -- www.havensconsulting.net/consu...
Online Courses -- www.havensconsulting.net/onli...
Contact & Support - www.havensconsulting.net/conta...
EMAIL US AT 📧
info@havensconsulting.net
#PowerBI #powerplatform #microsoft #businessintelligence #datascience #data #dataanalytics #datavisualization #dashboard #bi #analytics #powerquery #dax #datatypes #datamodeling #powerquery #dax 
Наука
THANK YOU so much for this. I had been pounding my head against my desk for the last 2-hours trying to figure out what the problem was. Bravo!
You are a God send!!! The way I have been stuck on this for 4 hours going back and fourth. Thank you so much.
Glad I could help!
Experienced that myself! I could not believe what was happening so i created a calculated column in dax with the year month and day of the column and worked! Now I understand why 😀
You are a hero! I bashed my head against this for an hour this morning before figuring out what question I needed to ask, which was answered right here! Thank you!
I wish I would have discovered this a week ago! I have been trying to join a Date field to a Date/Time field and had no idea why nothing was work. Thanks!!!!😀
Wow, this is great. Thanks Reid!
Brilliantly explained! Thank you for your elaboration and for being on the point! A+++
Fantastic video! Have wracked my brain all week on this issue! Much appreciated!
Thanks! This has been driving me nuts for years. I kinda understood what was going on, but your explanation put it in front of me where I could really see it.
Thank you! I wished PBID made it more clear about this issue, which is why I made this video.
@@HavensConsulting
I sat through this pain for about a day and tried to figure out what I did wrong and implemented so many solutions and it still didn't work until 15 mins ago when I accidentally found a comment in some power bi community website.
Some guy said "Open it with Power Query" so I tried it and I was like dying.
I mean how could someone figure this out?
In fact, Microsoft should put this issue in the first page of power bi training material saying that "DO NOT CHANGE DATE TIME DATA TYPE ON POWER BI BECAUSE IT'S USELESS AS HELL AND USE POWER QUERY INSTEAD, FOR THE LOVE OF GOD!!!"
I think this is the worst bug i ever found in power bi.
Have you ever encountered a worse bug than this one? 😂
@@norpriest521 Yeah sadly I agree. I've given candid feedback to the Power BI team that this needs to be updated. Since it's the ONLY type conversion you do that DOESN'T actually change the type. :\
OH MY GOSH - Thank you so much for this! Many, many hours later. As a Power BI beginner, this was so not obvious.
Thank you! Really helped me with my work! More blessing to you!!!
This is fantastic ! You made my day. Cheers
Oh Wow! You break this down into a great explanation and example! Thank you so much for this information!
What a great video that just saved me from hours of pulling my hair out. I completed the changes you suggest in the video and a model I created just all fell into line. Can't thank you enough.
Happy it helped!
Your are a lifesaver! Was driving me mad and this was the exact issue i was having and this solution worked a treat. Thank You! :)
Great video! I've found there are many nuances to making the most of time intelligence. Personally, I'm not fond of joining on a date - for this very reason. I've found that adding a column in Power Query with this simple formula (=Number.From()) converts my date column into an integer value. I use this column in my Date Table as primary key to join to other tables that have dates. The tables I join have a date foreign key column that uses the same formula. Now I'm joining on integer values, which is my personal preference. Keep up the great work!
Great idea, Chris. I’ll give that a try.
Thank you for saving me more hours of frustration.
Happy to help
I lovvvvvveeeeeeeee you, i've been literally looking for this a whole week in powerbi forums and i got nothing.
You got a new subscriber
I'm glad I could help! It's been a major pain for so many people! 😅
brilliant!!! you explained really well, I finally understood the problem! Thanks!!!
Glad it helped!
Many thanks for sharing this information! It helps!
Very grateful for this, thank you!
Thank you for this great explanation!
Great video, saved me a lot of time - thank you
You are an absolute lifesaver!!!!
This saved me so much time, I wish I saw this earlier. Thanks a lot!!!
Thank you - saved me so much time!
yes it's very help full video. made my day. Thank you so much
This is great, thank you!
OMG !!
For the last 3 hours, I am searching for this information. Man you saved me
I can’t thank you enough. !!!!
love from India
Glad I could help!
Thanks so much!! This was just what I was looking for.
You're very welcome! I'm glad you found it helpful.
super helpful and well explained! The ref article is a treasure!
Note, if any of the datetime columns contains a timestamp (e.g.yyyy,mm,dd,hh,nn,ss) then the other table should have hh,nn,ss in order to create a relationship.
thanks so much ! the last trick solved my issue! You are awesome!
Thank you SO much. This saved me!
amazing you just saved my sanity!
Really THANK YOU so much, this helped me a lot
Straight to the point, you saved my report ;) thanks!
Glad it helped!
Good one 👍. Thank you.
No joke... I love you man! THANK YOU. This video just helped me keep my sanity with this project (for now)
Glad I could help!
Thank you so much! Finally solved my issue!
You're welcome!
saved me a lot of thought! thanks!
Thank you so much. I've been battling with this issue for the past 3 days and could not figure out what I was doing wrong. Cheers!
Glad it helped!
Awesome!! 👏👏👏👏👏👏
That was very helpful. Thanks.
Glad it was helpful!
This solved my problem, thank you!
Happy to help!
You saved me. Thank you !!
You're welcome!
Not sure as to why, but using the locale option rather than just changing to date, which would result in errors, helped me achieve this.
Wow! I was also getting errors, and I was only able to make it work thanks to you comment :)
Thank you, thank you, thank you !!!!.
Thank you it's work
Great tip
Thanks a lot ... 🙏
Thanks bro! This video saved me!
Glad it helped!
Very helpful 🤠👍👍👍
I went crazy with one report until figured it out! Thanks Reid!
Right? It’s a really nuanced issue!
Thank you
Thanks - was thinking I was an idiot trying to get it to work until I saw this video - thanks alot
I realize that this is a bit older video, and that this information may already be known, but thought I would share anyway. In my case, the data sets I am working with are somewhat large and I am, for reasons I won't go into here, having to pull it over VPN as well, so modifying my Power Query was not something I wanted to do unless I had to. It occurred to me that there might be another option, which led me to an alternative work-around for this issue. If you create a new column with DAX (e.g. Created = [MyDateTimeColumn].[Date]) and format it as Date with the ShortDate format, you can use the date table without breaking the date field, or having to add tons of items to your date table to match granularity.
Great alternative! Yes if you can't apply a date extract in PQ, then a DAX column works just fine :)
I just wanted to say a huge thank you I was struggling with this a found it very difficult to find an answer.
So happy it helped! Honestly my favorite video I recorded just because of how it helps people
Life Saver
You saved my soul
The best response, you're welcome!
Now this make sense
Excelent content. However, I need the other way around and show Date/Time. How can I make Power BI Desktop understand the same Date/Time format?
Good question. If you have a fact table with both Date and Time dimensions it's recommended to have seperate dimension tables for each. Otherwise you'd need one row for every second between your start and stop date. That's 36 million rows per year! Here's a good article talking about splitting your date and time tables. radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model
Hi, if I have power query from t-sql that brings through datetime with zero seconds should I convert to date?
Definitely, I'd recommend converting any DateTimes to Date in Power Query before importing, if you don't need time, else best practice is having a separate Date and time Dim Table
Your help is appreciated, on my calendar date I see extra date compared to my fact table. Why is it not showing the same range? Thank you
Are you getting the min and max dates from your fact table or using the CALENDARAUTO function? If you make your calendar table using:
CALENDAR( MIN(Fact[Date]), MAX(Fact[Date]) )
Then your date range in the calendar table will match your fact table.
@@HavensConsulting I get the Max Date. Its working OMG. Thank you so much sir.
@@BigO_Notation glad to hear it! I'd check out my other calendar videos too! Plenty of good tips on making quality calendar tables
ruclips.net/video/AdLDYohLeJc/видео.html
@@havenscr definitely, I have been looking at your contents and will check out the rest. Thank you
Hope this works for 😢
Is it possible to keep the imported column as datetime, and then have a calculated column that uses only the date part, and then join on the calculated column?
Absolutely! You could add that column either through Power Query, or through DAX. Personally I recommend PQ because it compresses the data a bit better during the refresh/import process.
@@HavensConsulting So if I want to keep the time part of the datetime data, but still want to join to a calendar date field - would you say the best option is to create an extra date-only calculated field in PQ and use that to join back to the calendar table? My only concern is that I have quite a few dates in my data, so would end up with a lot of duplicated date only columns. Thanks for the reply as well!
@@JoeAverage2006 that’s correct. The only way to join to a date only calendar table is you’d need a date key that is also date only
@@havenscr Thank you! I'll start creating those date only columns to join on. It is interesting, there are many calendar tutorials out there but almost no one mentions this.
@@JoeAverage2006 sure thing! My video on issues with date time from earlier this year talks about this as well 🙂