Forum Discussion
DAX formula simplification possible?
JobvanAtten 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
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?