Rank - but only if the rows meet criteria

[s--turn]

Hello,

I am trying to achieve the results that I've added in red, in the final column.  i.e. I only want the rank to operate on rows that meet the criteria.  However, it seems to be operating on all rows, which is why in this example (filtered on a specific contactid) the ranking appears to start at 2. 

Is there a way to do this?  I have tried a few different techniques involving variables and virtual tables, but they never get anywhere!

Just to clarify what I'm trying to achieve, this is a table of memberships (membershipid) belonging to different contacts (contactid).  Many contacts appear more than once in the table, as they have a membership for each year.  I want to rank their memberships from earliest to latest - but I am only interested in the ones meeting my criteria of Grade = Graduate and Subgrade <> Free.  

Thank you in advance.

[DataNinja777 · Accepted solution]

Hi @s--turn ,

Your current DAX measure is almost there but it explains why you're seeing the rank start from 2 — it ranks all rows using RANK, but the condition inside the IF merely hides the rank value for those that don’t qualify (like Grade = Graduate and Subgrade = "Free"). This doesn’t exclude them from the ranking engine — it just blanks them out after the rank is calculated.

To fix this and make the ranking only apply to rows that meet your condition, you need to build a filtered table inside RANKX rather than use IF outside. Here’s the corrected version using RANKX and FILTER, which will ensure the ranks start from 1 and only apply to qualifying rows:

Paying Graduate Rank = 
VAR CurrentContact = Memberships[contactid]
VAR FilteredTable =
    FILTER(
        ALL(Memberships),
        Memberships[contactid] = CurrentContact &&
        Memberships[Grade] = "Graduate" &&
        Memberships[Subgrade] <> "Free"
    )
RETURN
IF(
    Memberships[Grade] = "Graduate" && Memberships[Subgrade] <> "Free",
    RANKX(
        FilteredTable,
        Memberships[blc_validto],
        ,
        ASC,
        DENSE
    )
)

This will:

Only rank rows where the Grade is Graduate and Subgrade is not Free.

Rank them per contactid, so the order restarts for each person.

Prevent skipped numbers in rank, because only valid rows are passed into RANKX.

Your current RANK function with PARTITIONBY works similarly to RANKX + FILTER, but because it doesn’t exclude the "Free" subgrade row before ranking, the first result is 2. This fix ensures your rank will always start from 1 for eligible rows.

Let me know if you'd like the result to show "Not Ranked" or "-" for non-qualifying rows instead of leaving them blank.

Best regards,

[s--turn]

Thanks SO MUCH @DataNinja777 !  That has worked!  I really appeciate it 🙂 

[lbendlin]

Use the Relation parameter, with the prefiltered rows/aggregations that you want.

[s--turn]

Thanks @lbendlin - I've tried that, but it says there is a circular dependency:

I've tried a few other ways of doing this, but nothing seems to be right!