CODEWITHSUNDEEP
performancedax
Kingsley Tan
Kingsley Tan

Reputation: 23

DAX measure with TOTALMTD running slow

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

Upvotes: 2

Views: 951

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

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.

enter image description here

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.

Upvotes: 1

Related Questions