Convert DAX Measure to SQL

[Anonymous]

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]))

[Anonymous · Accepted solution]

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.
    )
)

[themistoklis]

Hello @Anonymous 

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

[Anonymous]

thanks thenistoklis and daxer