Transformation help in Fabric Data Warehouse

[ludvigreck]

Hello,

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

TIA,

Ludvig Reck

[v-nuoc-msft · Accepted solution]

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.

[v-nuoc-msft]

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.

[ludvigreck]

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