Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@avisingh hey, I'm using your ultimate calendar but I can't seem to be able to link the date column with my "dateCompleted" column to be able to use quarter, month and year filter. can you please assist. i changed my dateCompleted type from date/time to date. but nothing seems to be working. I'm also trying to change the type of month from text to date but powerBi keeps giving me errors.
Please note that I'm importing my data from bigquery as a direct query.
Thank,
Solved! Go to Solution.
Hi @Anonymous ,
I see your date column has a format of 'yyyy-mm-dd HH:MM:SS UTC' and per my testing, that doesn't play well in PowerBI. What you can do is import it as text, create a new column based on that dateComplete column and removing the ' UTC' part of it, then change the type of the new column to DateTime.
Then create the relationship with the datetable, then drag the Date column from the datetable into your visual and that should work.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @Anonymous ,
Please confirm the following points.
1# The data type of "dateCompleted" is Date and there is an active relationship between "dateCompleted" and calendar[date].
2# Slicers are based on calendar table and you can get filtered data through the slicers instead of having no data.
3# If the Card visuals are based on measures, please make sure the measures are correct.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I can confirm #1 and #3.
About #2 i'm not able to change type to date. whenever i try to do so it changes all the rows to 'Err'. if i use the filters everything will be blank as shown in the screenshots
Hi @Anonymous ,
Just like djerro123 said, Power BI couldn't resolve the values to dates if the format was incorrect.
For example, 10/1/2019 would be October 1 2019 , 1/10/2019 would be January 10 2019 and 25/12/2019 would throw an error if your Locale for import is set to English(United States).
It would be better if you could share some sample data about "dateCompleted" and calendar[date] to us.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
@v-jayw-msft @JarroVGIT i changed the settings to English(Europe)
Here's a screenshot of the calendar and a screenshot of the datecompleted column from the data warehouse. still doesn't work.
Hi @Anonymous ,
I see your date column has a format of 'yyyy-mm-dd HH:MM:SS UTC' and per my testing, that doesn't play well in PowerBI. What you can do is import it as text, create a new column based on that dateComplete column and removing the ' UTC' part of it, then change the type of the new column to DateTime.
Then create the relationship with the datetable, then drag the Date column from the datetable into your visual and that should work.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
@Anonymous did this solve your issue? Please mark it as the solution so others may find the answer to the same issue. Thanks
Proud to be a Super User!
Hi @Anonymous ,
I just put in a reply on your other topic, but I think it might has the same problem. The problem is the following: you are loading data in which you think is in date format but actually isn't. Therefor, PowerBI cannot resolve the values to dates and throws errors. Think of it like this:
Date 01/13/2019 is a perfect valid US notation. But in the Netherlands, this would resolve in an error because this means the first of the 13th month of 2019.
What you can do is two fold: change the locale for import in your settings to something that is corresponding with the datasource. See the following screenshot.
Then, you can change the type to date(time) in powerquery and try again.
Proud to be a Super User!
What error message do you get when you try to create the relationship?
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
I didn't get any error it just doesn't work when I use the filters. so so basically i created the relationship and then used the calendar table as a date filter, this is when it doesn't work.
Highlighted in blue are the filters taken from the calendar table and everything else comes from my other table.
Relationship is between "Date" and "DateCompleted"
thanks,
Hmm I really need some more information. Would it be possible to share your PBIX? If you don't want to share it widely you can PM me the link and I will try and solve this for you.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
I can't share the file because I don't have authorization to so so (company) and you can't open it even if I share it because of database security.
Thank you for trying though!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |