SumX Formula Help for Optimal Performance

[Kevin_Wantz]

Hi All,

Hoping to receive some help on optimizing a SumX dax formula that currently is either not calculating and exceeding the total number of available resources, or calculates after taking 10 minutes minimum. The data model is a model to measure learning and development course completion for those in a specified target audience. For example, a course might only before Director's and Sr. Director's but another course may be geared towards the full company population. In essence, a curriculum can contain 10 or 20 different courses and I want a teammate to be counted as 1 if they completed all courses within the curriculum. Any ideas?

Meas.TotalCompleteALL =
SUMX (
    VALUES ( Audience[Course Name] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Course Completion'[Teammate ID] ),
        FILTER (
            'Course Completion',
            [Meas.TargetCountALL] >= 1
                && 'Course Completion'[Course Name (groups)] >= [Meas.MinCourse]
                && 'Course Completion'[Course Name (groups)] <= [Meas.MaxCourse]
                && 'Course Completion'[Completion Status ID (groups)] = "COMPLETE"
        )
    )
)

Meas.CurriculumCompleteNewHire =
IF ( [Meas.TotalCompleteALL] >= [Meas.Curriculum_NewHires], 1, 0 )

Meas.Curriculum_TotalCompleteAll_NewHire =
SUMX (
    FILTER (
        VALUES ( TeammateData[Teammate ID] ),
        [Meas.CurriculumCompleteNewHire] = 1
    ),
    [Meas.CurriculumCompleteNewHire]

[Anonymous]

First of all, you should post the data model.

Second of all, no wonder this takes forever... You should know that:

1. Using a measure inside an iterator (like FILTER) is very slow if the cardinality of the iterator is big. Hence, iteration with a measure over a fact table will almost always be agonizingly slow and you'll never live to see the end of the calculation.

2. Nesting iterations is also a very bad idea (depending on the cardinality of the iterated tables).

3. DAX will be fast and easy ONLY WHEN your model is correct and built following the best practices. If your model sucks, then you'll start writing DAX which nobody - even you - will understand, especially after a while. Do you want it?

4. If your model is right, then DAX will be SIMPLE. SIMPLE means: easy to understand and to make changes to.

First, please paste your full model in here. Second, if you can, please put the file on a shared drive (like OneDrive) and send us a link to it so that we can play with the code and the model.

Thanks.

Best

Darek