Forum Discussion
How to do sequential filtering for rankings
TopN 3 P/BV =
VAR SelectedTop = SELECTEDVALUE('TOPN 3'[Top N 3])
RETURN
CALCULATE(
SWITCH(
TRUE(),
SelectedTop = 0,
[_Rank Switch P/BV],
[_Rank Switch P/BV] <= SelectedTop,
[_Rank Switch P/BV]
),
SelectedTop will never be zero. You would need to use the standard measure trick
VAR SelectedTop = 0+SELECTEDVALUE('TOPN 3'[Top N 3])
Adjust your other measures accordingly. Note: You may need to compute your TOPN dynamically because the TOP N 3 filter should only allow you to select whatever has been prefiltered in Top N 2. (In your example Top N 3 should only allow you to select from 1 to 3 items, no?)
- LucaixBolha4 years ago
Resolver I
Ahh you're right lbendlin!
Indeed, Var SelectedTop won't be zero and TOP N 3 filter should only allow me to select whatever has been prefiltered in Top N 2. Thank you very much for your time, I'll try it out! 🙏🙌
- LucaixBolha4 years ago
Resolver I
lbendlin, could you please talk a little bit more about the standard measure trick (what is it for, etc.)?
- lbendlin4 years ago
Super User
If there is a threat that a measure could return BLANK() you can add a 0 to the measure output to return at least "something" without skewing the final result.
- LucaixBolha4 years ago
Resolver I
ahhh got it! Thanks a lot 😄