我设计了一套衡量标准,最终应该允许我计算没有取消交易和折扣的销售额
以下是这些措施的定义:
define measure Sales[Amount] = SUM(Amount)
define measure Sales[Discounted Amount] = CALCULATE(
ABS(SUM(Sales[DiscountValue] ) ),
FILTER(
VALUES( Sales[SalesOrderSource] ),
Sales[SalesOrderSource] = "XXX"
),
USERELATIONSHIP ( Sales, SalesInvoiceDate[SalesInvoiceDate] ),
USERELATIONSHIP ( Sales, SalesOrderDate[SalesOrderDate] ) --
)
define measure Sales[Cancelled Amount] = CALCULATE(
ABS([Amount]),
FILTER( VALUES( 'Sales' ), Sales[Status] = "Cancelled" ),
USERELATIONSHIP ( Sales[InvoiceDate], SalesInvoiceDate[SalesInvoiceDate] ),
USERELATIONSHIP ( Sales[OrderDate], SalesOrderDate[SalesOrderDate] ) --
)
-- RUNNING EXTREMELY SLOW
define measure Sales[AmountNet] = CALCULATE(
[Amount] - [Cancelled Amount] - [Discounted Amount],
USERELATIONSHIP ( Sales[InvoiceDate], SalesInvoiceDate[SalesInvoiceDate] ),
USERELATIONSHIP ( Sales[OrderDate], SalesOrderDate[SalesOrderDate] )
)
不幸的是,Sales[AmountNet]的最终度量的性能非常缓慢。
但当我从[AmountNet]定义中删除[Cancelled Amount]因子时,它表现得很好。我怀疑这是因为两次引用同一个度量值([Amount](,其中第二次引用被FILTER迭代器重载。
我希望在理解这种行为以及如何重写这种行为以获得更好的性能方面得到一些支持。
谢谢。
你能试试吗:
define measure Sales[Cancelled Amount] = CALCULATE(
ABS([Amount]),
KEEPFILTERS( Sales[Status] = "Cancelled" ),
USERELATIONSHIP ( Sales[InvoiceDate], SalesInvoiceDate[SalesInvoiceDate] ),
USERELATIONSHIP ( Sales[OrderDate], SalesOrderDate[SalesOrderDate] ) --
)
我希望这种方法将比FILTER over VALUES(Sales(更便宜。