Use a variable within a filter not working properly

[nicolasvargas]

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

[lbendlin]

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)

[nicolasvargas]

Hello, 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.