DAX measure with TOTALMTD running slow

Question

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

Is 'A' a Calendar/Date table? Note TOTALMTD is a Time Intelligence function which only works properly with a table marked as Date/Calendar. I think you can use explicit filters but you will have to provide data sample in order to help you. — alejandro zuleta
Hi alejandro, the 'A' is a fact table which contains a date column called 'Date'. I have uploaded the 'Sample Data' - the first column will be showing the original 'A'[Date] data; the second column is the first measurement i mentioned above; the third column will be the second measurement i mentioned above. — Kingsley Tan

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-01-24T13:27:29

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.

DAX measure with TOTALMTD running slow

1 Answers