optimizing dax distinct aggregate

Question

I have a tabular model that is mostly a star schema, except for date, which has no reltionship to the fact table.

The reason for this is all reports run are snapshot reports, i.e. where selectedDate between StartDate & EndDate, i.e. there is no direct relationship.

This is fine when calculating a distinct count of all ID's, but if a user drags a column onto the rows of a pivot table, to break out the result, excel freezes for a while before giving the "Memory error: Allocation Failure: The paging file is too small...." .

To optimize, i have created a number of calculated columns on my fact table, one for each filter in the measure, so the calculation doesn't have to query relationships & dimensions. I can do this for all except the date column.

The database is not big, about 700mb, and the aggregated distinct count is just under 4000 rows, so the result set is not big either, yet i still get the out of memory exception.

The calculation is below. I cant share the full dataset unfortunately:

CALCULATE (
countrows ( values('StudentResults' [StuIDNumber])     ),
FILTER (
'StudentResults',
( 
'StudentResults'[CourseInstanceStartDate] <= MIN ( 'Date'[DateID] )
&& 'StudentResults'[CourseInstanceEndDate] >= MIN ( 'Date'[DateID] )
) 
&& 'StudentResults'[CourseInstanceStatusCode] = "OPEN"
&& 'StudentResults'[StudentStartDateID] <> 19500101
&& 'StudentResults'[studentStatusCode] = "R" 
&& 'StudentResults'[Session] >= 2014
)
)

Id appreaciate any ideas on how to get this working? (Note it works with CountA(), or Count, but distinctCount or countrows(values()) causes this issue.

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-03-13T20:00:15

I've faced similar issues using COUNTROWS over virtual tables, ended up by using SUMX.

CALCULATE (
    SUMX ( DISTINCT ( StudentResults[StuIDNumber] ), 1 ),
    FILTER (
        StudentResults,
        (
            [CourseInstanceStartDate] <= MIN ( 'Date'[DateID] )
                && [CourseInstanceEndDate] >= MIN ( 'Date'[DateID] )
        )
            && [CourseInstanceStatusCode] = "OPEN"
            && [StudentStartDateID] <> 19500101
            && [studentStatusCode] = "R"
            && [Session] >= 2016
    )
)

I've seen a couple of question before regarding this issue and OPs expressed it improved the performance too hope it works for you. [1]

optimizing dax distinct aggregate

1 Answers