Need to update RankX measure to not rank blank rows

[Anonymous]

Hi,

I have a table visual in Power BI that I'm trying to sort a very specific way.  Here is the table with some dummy data as it is now:

As you can see, my [RANK] measure is ranking rows where the 'Total Plan Paid' amount is blank as 7

My problem is with the last column on the right 'Rank'.  Rank is a created measure I made:

Rank = RANKX ( ALLSELECTED ( 'Shock Claims Query'), [Total Plan Paid],,DESC )

I want/need to update my [Rank] measure to ignore the rows where the 'Total Plan Paid' amount is blank.  In simple terms, I want Rank to only rank rows where the 'Total Plan Paid' amount is a numerical value.  I need it to look something like this.  That way when I sort I can get the table to look like this:

[TheoC · Accepted solution]

Can you remove one of the commas before DESC and one of the brackets after?

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try to create a measure like this:

_rank1 = 
IF (
    NOT ( ISBLANK ( 'Table'[_Total Plan Paid] ) ),
    RANKX ( ALLSELECTED ( 'Table' ), [_Total Plan Paid],, DESC ),
    BLANK ()
)

If you do not want all blank values to be ranked the same, then add another measure.

_rank2 = 
var _t=ALLEXCEPT('Table','Table'[MemberlD])
var _rank=MAXX(_t,[_rank1])
return _rank

Result:

If furthermore you want the ranking of blank values to be as invisible as possible, then add an additional measure of font color conditional formatting.

_color = IF(ISBLANK('Table'[_Total Plan Paid]),"white")

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[TheoC]

Hi @Anonymous 

Try following measure:

Rank = 

IF ( NOT ( ISBLANK ( [Total Plan Paid] ) ) ,

RANKX ( FILTER ( ALLSELECTED ( 'Shock Claims Query'), [Total Plan Paid],,DESC ) ) ) ,

[Total Plan Paid] ) )

Hope this helps!

[Anonymous]

Thank you for the help so far!

I think that's close but I'm getting an error in the syntax:

I'm not sure we're using the FILTER function correctly.  This is pretty confusing.  I might have ti start a new thread with a clearer description.

[TheoC · Accepted solution]

Can you remove one of the commas before DESC and one of the brackets after?