Ranking with multiple filters

[Anonymous]

Hi I have a table and I want to rank ONLY the instances of userIDs where statusofResponse is 1 and ResponseID is 99. Here is the table and here is the desired output:

UserID	ResponseID	Response Value	StatusofResponse	DateUpdated
1	99	6	1	3/1/2020
2	99	9	1	3/3/2020
3	45	1000	1	3/3/2020
4	99		0	3/3/2020
1	99	3	1	3/10/2020
2	45	4000	1	3/10/2020
2	99	3	1	3/12/2020

Desired output

UserID	ResponseID	Response Value	StatusofResponse	DateUpdated	Rank
1	99	6	1	3/1/2020	1
2	99	9	1	3/3/2020	1
3	45	1000	1	3/3/2020
4	99		0	3/3/2020
1	99	3	1	3/10/2020	2
2	45	4000	1	3/10/2020	2
2	99	3	1	3/12/2020

So 3 wouldn't have a rank because the ResponseID was not 99 and 4 wouldn't be ranked because the statusofresponse was 0

thanks!

[Greg_Deckler · Accepted solution]

Try:

Rank = 
    IF([StatusofResponse] <> 1 || [ResponseID] <> 99,
        BLANK(),
        RANKX(FILTER('Table',[StatusofResponse] = 1 && [ResponseID] = 99 && [UserID] = EARLIER([UserID])),[Response Value])
    )