RankX in PowerPivot with criteria

[gmazza76]

Good afternoon,

I am trying to complete a RANKX that will only rank the individual lines that are greater than zero in "Column 1", but I am getting the following results
Example 1, all columns with 0 in are showing 1 or 2 even though there are 20 lines with the same "callid", but different "date_time"
Example 2, confirms ranking from 1 to 20 based on the "date_time"

Ideally I want the lines ranked if "Column 1" is greater than 0, but I want the rank to go 1, 2, 3 etc, leaving the other rows with a Blank as a result but not increasing the rank to count the row if it has a 0

Example 1
=RANKX (FILTER (ALL ( Sheet1 ),Sheet1[callid] = EARLIER ( Sheet1[callid])  && Sheet1[Calculated Column 1] > 0 ),Sheet1[date_time],,ASC)

Example 2
=RANKX(FILTER(ALL(Sheet1),Sheet1[callid]=EARLIER(Sheet1[callid])),Sheet1[date_time],,DESC,Dense)

Hopefully that helps, thanks in advance
Gavin

 

[RoryA]

Don't you just need to exclude them from doing the calculation?

=IF([Calculated Column 1] > 0,RANKX (FILTER (ALL ( Sheet1 ),Sheet1[callid] = EARLIER ( Sheet1[callid])  && Sheet1[Calculated Column 1] > 0 ),Sheet1[date_time],,ASC),BLANK())

 

[gmazza76]

thanks for this @RoryA,

It seems to give the correct output as per the code I had managed to cobble together which is great, but I have 1 quick question if you can.

I have realised I have a "user" id that I now need to add into the formula you have provided (as yours is cleaner) to only count it if the user id is different if that makes sense.
So if I have 2 users that have registered I want the earliest to be 1 and the next 2 if thats ok

mine
=if(Sheet1[Calculated Column 1]=0,BLANK(),RANKX (FILTER (ALL ( Sheet1 ),Sheet1[callid] = EARLIER ( Sheet1[callid]) && Sheet1[Calculated Column 1] <> 0 ),Sheet1[date_time],,ASC,Dense))

yours
=IF([Calculated Column 1] > 0,RANKX (FILTER (ALL ( Sheet1 ),Sheet1[callid] = EARLIER ( Sheet1[callid])  && Sheet1[Calculated Column 1] > 0 ),Sheet1[date_time],,ASC),BLANK())

thanks

 

[RoryA]

Can you post a sample showing what you want? I'm not really seeing how that wouldn't be what the current formula does anyway, since it ranks each row.