Forum Discussion
JobvanAtten
2 years agoFrequent Visitor
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...
BeaBF
Super User
2 years agoJobvanAtten Hi! I've tried to do some optimizations:
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.
Optimize Variable Usage: Employ variables to hold intermediate results, thereby minimizing recalculations and redundancy.
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