SQL to DAX

[Anonymous]

Hi everyone! How do you convert this querry into DAX? 

I want to standardize queuries and was hoping it's possible to have this one as a measure/table(s).

WITH cte_elevation AS ( 

    select well_id, SUM(datum_elevation) * -0.3048 AS elevation 

    from CD_DATUM 

    where is_default = 'Y' 

    group by well_id        

) 

,cte_plan AS ( 

    SELECT wp.well_id, 'Plan' as Type, (wp.md_to * -0.3048) + elevation AS Depth  

        ,cast(sum(isnull(wp.target_duration,0)) over (partition by wp.well_id order by cast(step_no as smallint)) as int) as hours 

        ,cast(step_no as int) AS OrderBy 

    FROM dbo.DM_WELL_PLAN_OP wp 

    JOIN cte_elevation e ON wp.well_id = e.well_id 

    WHERE target_duration is not null 

    AND md_to is not null 

) 

,cte_actual AS ( 

        SELECT d.well_id, 'Actual' AS Type, (d.md_current * -0.3048) + e.elevation AS Depth 

            ,isnull(days_on_location,0) * 24 as hours 

            ,CAST(date_report as int) AS OrderBy 

        FROM dbo.DM_DAILY d 

        JOIN cte_elevation e ON d.well_id = e.well_id 

) 

SELECT * FROM cte_plan 

UNION ALL 

SELECT * FROM cte_actual 

[amitchandak]

@Anonymous , See if this blog can help you how to build this one

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/