Forum Discussion
nicolasvargas
Helper I
Helper IUse a variable within a filter not working properly
Hello, I need to create a column which ranks a colum based on some conditions. I would like for these conditions to be included within the RANKX as a VAR instead of one by one to factorize my for...
lbendlin
Super User
Super UserYou have multiple rows per date. The Filter statement will then in some cases grab the wrong row and produce the wrong result. Note that you should use a variable for the date rather than using EARLIER.
Here's a refactored version that is "good enough".
Test2 =
VAR Universe = [a] > 0.2 && [b] < 0.9
VAR d = [date]
VAR DBCOUNT1 = RANKX(FILTER (Sheet1, ([Date] = d) && [a] > 0.2 && [b] < 0.9),[c],,ASC,DENSE)
RETURN IF(Universe,DBCOUNT1,-1)
Not really different from
Test1 =
VAR d = [date]
VAR DBCOUNT1 = RANKX(FILTER (Sheet1, [Date] = d && [a] > 0.2 && [b]<0.9),[c],,ASC,DENSE)
RETURN IF([a] > 0.2 && [b]<0.9,DBCOUNT1,-1)
nicolasvargasHello, but here you're not using the "Universe" variable within the filter. And that is what I'm trying to accomplish. Any ideas?
- lbendlin
In this particular scenario a variable won't help as the result is different for each row.