Forum Discussion
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 formula.
The current formula I have is this one:
Test1 =
VAR Al = 0.2
VAR DBCOUNT1 = RANKX(
FILTER (Sheet1, Sheet1[Date] = EARLIER(Sheet1[Date]) && Sheet1[a] > Al && Sheet1[b]<0.9),
Sheet1[c],,ASC,DENSE)
RETURN
IF(Sheet1[a] > Al && Sheet1[b]<0.9,DBCOUNT1,-1)
I would like to factorize the conditions on the filter and the if at the bottom with something like this:
Test2 =
VAR Al = 0.2
VAR Universe = IF(Sheet1[a] > Al && Sheet1[b]<0.9,1,0)
VAR DBCOUNT1 = RANKX(
FILTER (Sheet1, Sheet1[Date] = EARLIER(Sheet1[Date]) && Universe=1),
Sheet1[c],,ASC,DENSE)
RETURN
IF(Universe=1,DBCOUNT1,-1)
However when I apply this column Test2 outputs a wrong data. How can i do this?
This is my data and the outputs:
| date | a | b | c | Test1 | Test2 |
| 1/1/2022 | 0.05 | 0.78 | 0.03 | -1 | -1 |
| 1/1/2022 | 0.09 | 0.93 | 0.54 | -1 | -1 |
| 1/1/2022 | 0.19 | 0.19 | 0.16 | -1 | -1 |
| 1/1/2022 | 0.33 | 0.78 | 0.57 | 4 | 8 |
| 1/1/2022 | 0.41 | 0.12 | 0.89 | 5 | 10 |
| 1/1/2022 | 0.44 | 0.97 | 0.44 | -1 | -1 |
| 1/1/2022 | 0.62 | 0.41 | 0.3 | 3 | 5 |
| 1/1/2022 | 0.65 | 0.63 | 0.21 | 1 | 3 |
| 1/1/2022 | 0.74 | 0.74 | 0.29 | 2 | 4 |
| 1/1/2022 | 0.8 | 0.98 | 0.76 | -1 | -1 |
| 2/1/2022 | 0.03 | 0.02 | 0.39 | -1 | -1 |
| 2/1/2022 | 0.06 | 0.2 | 0.59 | -1 | -1 |
| 2/1/2022 | 0.22 | 0.17 | 0.8 | 6 | 9 |
| 2/1/2022 | 0.33 | 0.28 | 0.02 | 1 | 1 |
| 2/1/2022 | 0.57 | 0.56 | 0.91 | 7 | 10 |
| 2/1/2022 | 0.64 | 0.5 | 0.57 | 4 | 6 |
| 2/1/2022 | 0.69 | 0.34 | 0.02 | 2 | 2 |
| 2/1/2022 | 0.78 | 0.53 | 0.71 | 5 | 8 |
| 2/1/2022 | 0.82 | 0.04 | 0.12 | 3 | 3 |
| 2/1/2022 | 0.9 | 0.96 | 0.36 | -1 | -1 |
| 3/1/2022 | 0.03 | 0.45 | 0.96 | -1 | -1 |
| 3/1/2022 | 0.19 | 0.96 | 0.38 | -1 | -1 |
| 3/1/2022 | 0.2 | 0.73 | 0.02 | -1 | -1 |
| 3/1/2022 | 0.39 | 0.64 | 0.53 | 2 | 4 |
| 3/1/2022 | 0.43 | 0.43 | 0.68 | 4 | 6 |
| 3/1/2022 | 0.59 | 0.62 | 0.92 | 7 | 9 |
| 3/1/2022 | 0.67 | 0.46 | 0.46 | 1 | 3 |
| 3/1/2022 | 0.86 | 0.41 | 0.65 | 3 | 5 |
| 3/1/2022 | 0.88 | 0.73 | 0.79 | 5 | 7 |
| 3/1/2022 | 0.9 | 0.6 | 0.91 | 6 | 8 |
pd: My formula is way larger and has more conditions, this is just a sample to help solve the main issue I have
3 Replies
- lbendlin
Super User
You 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.
