使用TOTALMTD运行缓慢的DAX度量 [英] DAX measure with TOTALMTD running slow

问题描述

我的表格立方体中有两个度量值。

第一个度量值

 天数：= CALCULATE（COUNTROWS（SUMMARIZE（'A'[Date]）））
  

第二个将包括第一个表达式

 天数（MTD）： = CALCULATE（TOTALMTD（[天数]，'A'[日期]））
  

第二个当我浏览多维数据集并拉出该度量时，请测量该度量。
它的运行速度非常慢。

有人知道如何优化这些测量并使其运行得更快吗？

更新2：似乎您需要计算累计总数，情况下，仅将下面的表达式用于第二种度量即可：

 天数（MTD）：= 
计算（[天数]，过滤器（全部（A），[日期] < = MAX（A [Date]））））
  

更新3：使用SUMX和DISTINCT来计算不同的日期。

将以下第一项替换为：

 天数= SUMX（DISTINCT（A [Date]），1）
  

此解决方案可能比使用COUNTROWS + SUMMARIZE（
）性能更高，但是根据行数和运行它的
机器的不同，它可能会非常慢。

让我知道是否有帮助。

I have two measures in my tabular cube.

The first one called

'Number of Days' := CALCULATE(COUNTROWS(SUMMARIZE('A'[Date])))

The second one will includes the first one as its expression

'Number of Days (MTD)' := CALCULATE(TOTALMTD([Number of Days],'A'[Date]))

The second measure when I browse the cube and pull out the measure. It runs incredibly slow.

Any idea how I can optimize these measurements and make it run faster?

Sample Data

Volume:= SUMX(A, DIVIDE([Volume],2))

Volume (MTD):= TOTALMTD([Volume],'A'[Date])

Updated extra measurements

解决方案

The best practice should be creating a Calendar/Date table and use TOTALMTD Time Intelligence function. However this approach can be used if your model doesn't include a Date table.

First measure, number of days:

Num of Days := DISTINCTCOUNT(A[Date])

Cumulative measure:

Num of days (MTD) :=
CALCULATE (
    [Num of Days],
    FILTER (
        ALL ( A ),
        [Date] <= MAX ( A[Date] )
            && MONTH ( [Date] ) = MONTH ( MAX ( [Date] ) )
            && YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
    )
)

UPDATE: Added screenshot.

UPDATE 2: It seems you need to calculate a cumulative total, in that case just use the below expression for the second measure:

Num of days (MTD) :=
CALCULATE ( [Num of Days], FILTER ( ALL ( A ), [Date] <= MAX ( A[Date] ) ) )

UPDATE 3: Usuing SUMX and DISTINCT to count distinct dates.

Replace the first measure by the following:

Num of Days = SUMX(DISTINCT(A[Date]), 1)

This solution could be more performant than use COUNTROWS + SUMMARIZE, however it could be very slow depending on the number of rows and the machine where it is running.

Let me know if this helps.

这篇关于使用TOTALMTD运行缓慢的DAX度量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！