Forum Discussion

JobvanAtten's avatar
JobvanAtten
Frequent Visitor
2 years ago

DAX formula simplification possible?

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
)

4 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      JobvanAtten
      Frequent Visitor
      2 years ago

      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.

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      JobvanAtten
      Frequent Visitor
      2 years ago

      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?