Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have a very large table that I have to use direct query. The source is SQL Server. One of the fields is a datetime. When I connect to this source, it reads the field as datetime. This doesn't work in the model because my date dimension must join on a date, not datetime. When I cast, convert, use dartpart (doesn't matter), the field is either 1. a datetime, or 2. a string. Either way, for the date dimesion to work, it must be date. I changed the datatype from datetime to date in power query and it says "the step results in a query that is not supported in direct query mode."
I have seen some post as a workaround to create a view with the field as a date, then use that view. That is not possible as I don't have access to do that. Looking for a way to pull this field in as a date with the requirements as mentioned above.
Solved! Go to Solution.
Hi @_power_bi,
I will try one of this possible solutions:
1 - When conecting to SQL server use a query like this:
SELECT
YourTable.*,
CAST(YourDateTimeColumn AS DATE) AS NewDateColumn
FROM YourTable
2 - Add a Calcutated column using DAX and use it do make the realationship.
Proud to be a Super User!
Hi @_power_bi
Right-Click the datetime column and select Change Type then click date in power query.
Currently, the built-in date hierarchy is not yet available when using DirectQuery mode. You could click to upvote this idea or add your own comments.
As a workaround, you could create a custom date hierarchy manually. Please create [Year], [Quarter], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Quarter], [Month], [Day] columns to place them under [Date] column.
Why there is no date hierarchy - Microsoft Fabric Community
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @_power_bi,
I will try one of this possible solutions:
1 - When conecting to SQL server use a query like this:
SELECT
YourTable.*,
CAST(YourDateTimeColumn AS DATE) AS NewDateColumn
FROM YourTable
2 - Add a Calcutated column using DAX and use it do make the realationship.
Proud to be a Super User!
After some digging, I found two solutions. The first is what is proposed here. You can make a calculated column using DAX and form the relationship with the date dimension using it. However, I am not sure on performance on that and have not tested it.
The other method which I think will work better is to create a datetime column in the date dimension table. The column will display midnight for each day. This can be related to the datetime field in the table. *The dates in this column were all at midnight. This would not work if they were coming through with the datetime.
I already tried case (column_name as date) . It still reads it as a datetime. see original post.
You can create a calculated column
Date_Only =
DATE(YEAR('YourTable'[DatetimeField]), MONTH('YourTable'[DatetimeField]), DAY('YourTable'[DatetimeField]))
After creating this column, you can now use this column (Date_Only) to join with your Date dimension table.
If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
"the step results in a query that is not supported in directy query mode"
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
102 | |
49 | |
42 | |
39 | |
38 |