Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]))
Solved! Go to 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.
)
)
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.
)
)
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