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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Convertir medida de DAX a SQL

Hay expertos

¿Cuál es el equivalente SQL de la siguiente fórmula DAX (Power BI)
AOV = CALCULATE(SUM(FACTSalesOrderTable[Gross_Order_Value]),ALLEXCEPT(FACTSalesOrderTable,FACTSalesOrderTable[increment_id]))

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

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
Syndicate_Admin
Administrator
Administrator

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

Hola @Route217

Debe utilizar una función de ventana.

Pruebe algo como lo siguiente:

escoger dimensión1

, dimensión2,

SUMA(Gross_Order_Value) sobre (partición por increment_id) como AOV

De FACTSalesOrderTable

gracias thenistoklis y daxer

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors