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.

score 0 · Answer 1 · edited May 23 '17 at 12:25

0

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]

edited May 23 '17 at 12:25

Community

1
1

answered Mar 13 '17 at 20:00

alejandro zuleta

13,962
3
28
48

I tried that and i got the same "memory error: allocation failure" error, so although it may be more performant than distinctCount, not enough for this particular situation. I managed to get the DistinctCount to work by converting the STUIDNumber from a string to a whole number. The space requirements seemed to be much less, however the query still took about 2 minutes to run which is not acceptable. I went back to the old way, using Summarize. Its more complex than calculate and i was worried i was not considering all outcomes, but i have done quite a bit of testing and it seems ok. – JD_Sudz Mar 14 '17 at 10:57

optimizing dax distinct aggregate

1 Answers1