cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Route217
Post Prodigy
Post Prodigy

Convert DAX Measure to SQL

Hi Experts

 

What is the SQL equivalent of the following DAX (Power BI) Formula
AOV = CALCULATE(SUM(FACTSalesOrderTable[Gross_Order_Value]),ALLEXCEPT(FACTSalesOrderTable,FACTSalesOrderTable[increment_id]))

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

AOV =
CALCULATE(
    SUM( FACTSalesOrderTable[Gross_Order_Value] ),
    ALLEXCEPT(
        FACTSalesOrderTable,
        FACTSalesOrderTable[increment_id]
    )
)

// is equivalent to this SQL query:

SELECT
    sum( so[Gross_Order_Value] )
from dbo.FACTSalesOrderTables as so
where (
    so.increment_id in (       
//     1. if there is an active filter on increment_id
//        then you need all the values that the filter
//        uses. All other filters are removed from the
//        expanded fact table.
//     2. if there's no active filter on increment_id
//        this where clause must disappear from the
//        query completely.
//     This is the semantics of ALLEXCEPT.
    )
)

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

AOV =
CALCULATE(
    SUM( FACTSalesOrderTable[Gross_Order_Value] ),
    ALLEXCEPT(
        FACTSalesOrderTable,
        FACTSalesOrderTable[increment_id]
    )
)

// is equivalent to this SQL query:

SELECT
    sum( so[Gross_Order_Value] )
from dbo.FACTSalesOrderTables as so
where (
    so.increment_id in (       
//     1. if there is an active filter on increment_id
//        then you need all the values that the filter
//        uses. All other filters are removed from the
//        expanded fact table.
//     2. if there's no active filter on increment_id
//        this where clause must disappear from the
//        query completely.
//     This is the semantics of ALLEXCEPT.
    )
)

View solution in original post

themistoklis
Super User II
Super User II

Hello @Route217 

 

You should use a window function.

 

Try something like the following:

 

SELECT dimension1

            , dimension2,

            SUM(Gross_Order_Value) OVER (PARTITION BY increment_id) AS AOV

FROM FACTSalesOrderTable

thanks thenistoklis and daxer

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors