SQL to DAX conversion

[yasbui]

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!

[v-yuta-msft]

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

[Greg_Deckler]

Can you give us some sense of your data (sample data) and expected output? Also, purpose would be good, I'm not entirely sure if what you really need is DAX or M or just paste this SQL code into your query.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490