Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago
    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.
    )
)

3 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      thanks thenistoklis and daxer

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    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.
    )
)