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 tabl...
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.