Forum Discussion
Use 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
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)Hello, but here you're not using the "Universe" variable within the filter. And that is what I'm trying to accomplish. Any ideas?
In this particular scenario a variable won't help as the result is different for each row.
