DAX formula simplification possible?

[JobvanAtten]

Hi,

I have about 15 measures to calculate a rank for each user. At the end I sum these measures. Because of the complexity I run into a Resources Exceded error when I try to further filter a table or matrix containing the sum of these measures.

My goal is to calculate a rank for all scores lower than 0 in descending order and to calculate a rank for scores greater than or euqal to 0 in ascending order. These ranks are further computed to fall between -2 and +2.

Because users can be part of more than 1 team over time I calculate the temporary tables over all of the DIM User table.

Am I overthinking in my approach?

Measure:

Logistiek Score KlR = IF (

    ISBLANK ( [Logistiek Diff] ),

    BLANK (),

    VAR tbllaag =

        CALCULATETABLE (

            FILTER (

                SUMMARIZECOLUMNS ( 'DIM Users'[Naam], "@Diff", [Logistiek Diff] ),

                [@Diff] < 0

            ),

            REMOVEFILTERS ( 'DIM Users' )

        )

    VAR nlaag =

        -1 * COUNTROWS ( tbllaag )

    VAR tblhoog =

        CALCULATETABLE (

            FILTER (

                SUMMARIZECOLUMNS ( 'DIM Users'[Naam], "@Diff", [Logistiek Diff] ),

                [@Diff] >= 0

            ),

            REMOVEFILTERS ( 'DIM Users' )

        )

    VAR nhoog =

        COUNTROWS ( tblhoog )

    VAR Result =

        CALCULATE (

            2

                * SWITCH (

                    TRUE,

                    [Logistiek Diff] < 0, RANKX ( tbllaag, [Logistiek Diff] ) / nlaag,

                    [Logistiek Diff] >= 0, RANKX ( tblhoog, [Logistiek Diff],, ASC ) / nhoog

                ),

            ALL ( 'DIM Users'[Team] )

        )

    RETURN

        Result

)

[BeaBF]

@JobvanAtten Hi! I've tried to do some optimizations:

1. Divide the Calculation: Distinguish the logic for determining the rank of scores below 0 from that for scores equal to or above 0 by creating two distinct measures. This aids in debugging and enhances comprehension of the logic.

2. Optimize Variable Usage: Employ variables to hold intermediate results, thereby minimizing recalculations and redundancy.

3. Merge the Outcomes: Once the ranks are computed separately, integrate the results.

Logistiek Score KlR = 
IF (
    ISBLANK ( [Logistiek Diff] ),
    BLANK (),
    VAR NegativeScores =
        CALCULATETABLE (
            ADDCOLUMNS (
                VALUES ( 'DIM Users'[Naam] ),
                "@Diff", [Logistiek Diff]
            ),
            [@Diff] < 0
        )
    VAR NegativeCount = COUNTROWS ( NegativeScores )
    
    VAR PositiveScores =
        CALCULATETABLE (
            ADDCOLUMNS (
                VALUES ( 'DIM Users'[Naam] ),
                "@Diff", [Logistiek Diff]
            ),
            [@Diff] >= 0
        )
    VAR PositiveCount = COUNTROWS ( PositiveScores )
    
    VAR RankNegative = 
        IF (
            [Logistiek Diff] < 0,
            RANKX ( NegativeScores, [Logistiek Diff] ),
            BLANK ()
        )
    VAR RankPositive = 
        IF (
            [Logistiek Diff] >= 0,
            RANKX ( PositiveScores, [Logistiek Diff], , ASC ),
            BLANK ()
        )
    
    VAR Result = 
        SWITCH (
            TRUE,
            [Logistiek Diff] < 0, 2 * RankNegative / NegativeCount,
            [Logistiek Diff] >= 0, 2 * RankPositive / PositiveCount
        )
    
    RETURN Result
)

BBF

[JobvanAtten]

Thanks for the insights. CALCULATETABLE does not seem to work in this context to create NegativeScores and PositiveScores:

When I replace it with FILTER the temporary table does get built.
The end result however is not correctly calculated at the row level:

Any insights on what I am missing?

[Sahir_Maharaj]

Hello @JobvanAtten,

Can you please try this revised version of your measure:

Logistiek Score KlR = 
VAR LogistiekDiff = [Logistiek Diff]
VAR Naam = 'DIM Users'[Naam]

VAR tbllaag =
    CALCULATETABLE (
        SUMMARIZECOLUMNS ( 
            'DIM Users'[Naam], 
            "@Diff", [Logistiek Diff] 
        ),
        REMOVEFILTERS ( 'DIM Users' ),
        [@Diff] < 0
    )

VAR nlaag =
    COUNTROWS ( tbllaag )

VAR tblhoog =
    CALCULATETABLE (
        SUMMARIZECOLUMNS ( 
            'DIM Users'[Naam], 
            "@Diff", [Logistiek Diff] 
        ),
        REMOVEFILTERS ( 'DIM Users' ),
        [@Diff] >= 0
    )

VAR nhoog =
    COUNTROWS ( tblhoog )

VAR RankLow =
    IF ( 
        LogistiekDiff < 0, 
        RANKX ( tbllaag, [@Diff] ),
        BLANK ()
    )

VAR RankHigh =
    IF ( 
        LogistiekDiff >= 0, 
        RANKX ( tblhoog, [@Diff], , ASC ),
        BLANK ()
    )

VAR Result =
    SWITCH (
        TRUE,
        LogistiekDiff < 0, 2 * RankLow / nlaag,
        LogistiekDiff >= 0, 2 * RankHigh / nhoog
    )

RETURN
    IF (
        ISBLANK ( LogistiekDiff ),
        BLANK (),
        CALCULATE ( Result, REMOVEFILTERS ( 'DIM Users'[Team] ) )
    )

Let me know if you might require any further assistance.

[JobvanAtten]

Hi Sahir, thanks for the assistance. The combination CALCULATETABLE and 

SUMMARIZECOLUMNS does not seem to work:

If I understand variables in DAX correctly the VARs at the start get only calculated once and as a result are not usable for filtering further in the measure.