Forum Discussion

yasbui's avatar
yasbui
Frequent Visitor
8 years ago

SQL to DAX conversion

Hi all

 

I've got quite a heavy SQL query and need help on getting the DAX equivalent. Any responses would be appreciated:

 

select sum(lost_sales) as lost_sales, event from

(select event, created, channel, lost_sales from

(select event, sum(lost_sales) as lost_sales, created, channel, row_number() over (partition by event,channel order by created DESC) rn from

(select TO_DATE(event_date) event,round(sum(lostsales_collected + wwy_collected)) as lost_sales, channel, TO_DATE(createts) created from dashboard_data.dashboard_demcap_pres where TO_DATE(event_date) between TO_DATE(date_sub(current_timestamp(),35)) and TO_DATE(current_timestamp()) group by channel, event,createts

UNION ALL

select TO_DATE(event_date) event,sum(oos) as lost_sales, "TILL" as channel, TO_DATE(createts) created from  dashboard_data.dashboard_demcap_pres where TO_DATE(event_date) between TO_DATE(date_sub(current_timestamp(),35)) and TO_DATE(current_timestamp()) group by channel, event, created) A group by channel, event, created order by channel, event ASC, created DESC) t1

where rn = 1 order by channel, event ASC, channel DESC) t2 group by event order by event desc;

 

Thank you!

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi yasbui ,

     

    Here I list some points like below which may help you:

     

    "summarize(with aggregation nested)" in dax is equal to "select ... from ... group by ..." in sql

    "summarize(without aggregation nested)" in dax is equal to "select ... over(partition by ...) from ..." in sql

    "order by" in dax is equal to "order by" in sql

    "filter(table, condition)" in dax is equal to "where" in sql

    "union" in dax is equal to "union all" in sql

    "rank(dense)" in dax is equal to "row_number()" in sql

    "format([date], "YYYY-MM-DD")" in dax is equal to "TO_DATE" in sql

    "now()" in dax is equal to "current_timestamp()" in sql

     

    More details please refer to: From-SQL-To-DAX, DAX-Function-Reference.

     

    Hope it's helpful to you.

     

    Regards,

    Jimmy Tao