Forum Discussion
Subtract two values based on Rank
- 1 year ago
Ok so in that case the visual must already have been filtered to the test type. If you have multiple tests per person, within the same type, how are you filtering it to show the 2 most recent results? If you are not filtering it, and you are using measures to display columns 2 & 3 of your visual, you'll need to factor this into the DAX, I suggest something like this:
VAR _name = SELECTEDVALUE ( 'Table'[Name] ) VAR _test_type = SELECTEDVALUE( 'Table'[Test] ) VAR _recent_test = MAX ( 'Table'[Testrang] ) VAR _recent_test_score = CALCULATE ( SUM ( 'Table'[Wert] ), REMOVEFILTERS ( 'Table'[Testrang] ), 'Table'[Testrang] = _recent_test && 'Table'[Name] = _name && 'Table'[Test] = _test_type ) VAR _prev_test = _recent_test - 1 VAR _prev_test_score = CALCULATE ( SUM ( 'Table'[Wert] ), REMOVEFILTERS ( 'Table'[Testrang] ), 'Table'[Testrang] = _prev_test && 'Table'[Name] = _name && 'Table'[Test] = _test_type ) VAR _current_minus_prev = _recent_test_score - _prev_test_score RETURN _current_minus_prevIf this works for you please accept as the solution for visibilty of others.
Ok so in that case the visual must already have been filtered to the test type. If you have multiple tests per person, within the same type, how are you filtering it to show the 2 most recent results? If you are not filtering it, and you are using measures to display columns 2 & 3 of your visual, you'll need to factor this into the DAX, I suggest something like this:
VAR _name =
SELECTEDVALUE ( 'Table'[Name] )
VAR _test_type =
SELECTEDVALUE( 'Table'[Test] )
VAR _recent_test =
MAX ( 'Table'[Testrang] )
VAR _recent_test_score =
CALCULATE (
SUM ( 'Table'[Wert] ),
REMOVEFILTERS ( 'Table'[Testrang] ),
'Table'[Testrang] = _recent_test
&& 'Table'[Name] = _name
&& 'Table'[Test] = _test_type
)
VAR _prev_test = _recent_test - 1
VAR _prev_test_score =
CALCULATE (
SUM ( 'Table'[Wert] ),
REMOVEFILTERS ( 'Table'[Testrang] ),
'Table'[Testrang] = _prev_test
&& 'Table'[Name] = _name
&& 'Table'[Test] = _test_type
)
VAR _current_minus_prev =
_recent_test_score - _prev_test_score
RETURN
_current_minus_prev
If this works for you please accept as the solution for visibilty of others.
This works perfectly! I just had to change -1 to +1, because most recent test has the 1, the penultimate the 2 and so on.
- mark_endicott1 year agoSuper User
Valentin09 - No problem, I missed it saying the current test was 1 in your original description.
I'm glad you've been able to amend my DAX accordingly.