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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
tomq04
Helper I
Helper I

Adding/Subtracting Hours from a date from Redshift Source using DAX

I'm currently trying to subtract 8 hours from a date/time stamp hosted in Redshift.  This function works just fine when conntected to a SQL Server source (using literally the same table) but it is only subtracting 29 seconds from the Redshift table.

 

I'm currently using a last ditch effort  DAX formula (Adjusted_Posted = list_financial_events[posted_date] -(8/24)), but we have tried several iteration in both Power Query and DAX to perform this function, with the result always being -29 seconds.

 

Thanks,

 

Tom

1 ACCEPTED SOLUTION

Hi @tomq04,

Could you please use import mode to upload some records of data from Redshift to Power BI Desktop, then check if the issue persists?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-yuezhe-msft
Employee
Employee

Hi @tomq04

Could you please post sample data of your table? In Query Editor of Power BI Desktop, you can add a custom column using the following formula. You can view the example in the screenshot below.

[DateColumn]-#duration(0,8,0,0)

1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That did work, but it knocks us out of direct query mode.  I'll see if that is acceptable for our use case.

I mispoke earlier, I had been connected to our SQL server data warehouse.  Attached is what is happening when connected to our Redshift Server.  redshift error

Hi @tomq04,

Do you use import or DirectQuery mode to connect to Redshift?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

We are using direct query, the table we are working with is >15mm records.

Hi @tomq04,

Could you please use import mode to upload some records of data from Redshift to Power BI Desktop, then check if the issue persists?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

Even after importing the data we are still having the same issue where only 29 seconds is being subtracted from the date.

 

Screenshot

Hi @tomq04,

Could you please share me the PBIX file which you use import mode to connect to Redshift?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I sent you a private message with the onedrive link.  Let me know if that does not work.

 

Thanks

Hi @tomq04,

I am not able to reproduce your issue in your PBIX file, please check the following screenshot. Do you use the latest version of Power BI Desktop?
1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

You are correct this functionality works when imported.  After talking with a technician we determined that we needed to create an idea ticket for the functionality to work with Direct Query with a Redshift data source.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.