topic Re: Transformation help in Fabric Data Warehouse in Pipelines

Transformation help in Fabric Data Warehouse

ludvigreck — Wed, 21 Aug 2024 06:52:00 GMT

Hello,

Can anyone help me with "translating" this DAX code, so the transformation can happen at the data warehouse-level?

TIA,

Ludvig Reck

Re: Transformation help in Fabric Data Warehouse

Anonymous — Thu, 22 Aug 2024 02:06:11 GMT

Hi ludvigreck

VAR star = SELECTEDVALUE('Table'[Appointment Time]) // Query the date/time of the current row.

VAR DUR = SELECTEDVALUE('Table'[Duration]) // Query the Duration of the current row.

VAR nextStar = CALCULATE( MIN('Table'[Appointment Time]), ALLSELECTED('Table'), 'Table'[Appointment Time] > star ) // Query the next date/time.

VAR TimeDiff = DATEDIFF(star, nextStar, MINUTE) //Query star and nextstar minute difference

VAR final = IF( TimeDiff < DUR, TimeDiff, DUR ) // If timediff is less than the current duration, return timediff. Otherwise, duration is returned.

RETURN final //Return the final result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Transformation help in Fabric Data Warehouse

ludvigreck — Thu, 22 Aug 2024 06:31:48 GMT

Hello Nono Chen, you have posted the exact same DAX code as I provided ( ? )

To my understanding, this is not supported as a query language in Fabrics' Data Warehouse. I need this in a query languange so that I'm able to make this transformation at the Data Warehouse-level.

// Ludvig

Re: Transformation help in Fabric Data Warehouse

Anonymous — Thu, 22 Aug 2024 07:35:14 GMT

Hi ludvigreck

I misunderstood your question, please let me try to help you again.

Translating DAX code to SQL for data warehouse-level transformations involves converting the logic and functions used in DAX to their SQL equivalents.

WITH CTE AS ( SELECT [Appointment Time] AS minDate, Duration AS dur, LEAD([Appointment Time]) OVER (ORDER BY [Appointment Time]) AS nextStart FROM Query ) SELECT minDate, dur, CASE WHEN nextStart IS NOT NULL THEN DATEDIFF(MINUTE, minDate, nextStart) ELSE NULL END AS [Time Diff calc] FROM CTE

This SQL query should help you achieve the same transformation at the data warehouse level.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.