Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
yasbui
Frequent Visitor

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!

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.