confused by Rankx

[TheJaks]

hi all

in my table Payments, I'm trying to rank the invoices per PolicyID, such that the very oldest invoice is ranked 1, the 2nd 2 and so on. The only thing I'd like to do in addition, is not rank those rows where there isn't an invoice date yet (blank). I am trying the following code:

InvoiceRank =

RANKX (

    FILTER (

        Payments,

        NOT(ISBLANK(Payments[ActInvoiceDate])) && Payments[PolicyID] = EARLIER(Payments[PolicyID])

    ),

    Payments[ActInvoiceDate],

    ,

    ASC,

    Dense

)

This still ranks the blank invoices.

Any ideas why this may not give the expected results? Thanks

EDIT

I think this seems to work, not 100% sure I understand why 😄

IF (

    NOT(ISBLANK(Payments[ActInvoiceDate])), RANKX (

    FILTER (

        Payments,

         Payments[PolicyID] = EARLIER(Payments[PolicyID])  && NOT(ISBLANK(Payments[ActInvoiceDate]))

    ),

    Payments[ActInvoiceDate],

    ,

    ASC,

    Dense

))

[Anonymous · Accepted solution]

Thanks @lbendlin 

Hi, @TheJaks 

I think you can understand along the lines below:

First, in your first expression, you try to exclude with Payments[ActInvoiceDate] as null, and then calculate the rank:

In a calculated column, rankx sorts the entire table. The filter in Rank restricts the sorting context and does not remove the actual blank rows from the table. So in my case, the first 3 rows get the correct sorting. However, rows with an empty ActInvoiceDate are still sorted.

When I delete the mouse selection condition in column2, the sorting will change, which is a good illustration of the use of filter in Rankx to limit the context of sorting:

The reason why your second expression works is that you used the IF function to check if ActInvoiceDate is empty, sort if it is not empty, otherwise it will show empty. Here, your rankx is sorting for rows where ActInvoiceDate is not empty, and the context of the sort depends on the context of the filter restriction in Rankx. That's why this code works the way you intended.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Are Policy IDs temporally sorted?  Can you rank by dates instead?