Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AW1976NOVA
Post Patron
Post Patron

Need to update RankX measure to not rank blank rows

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:

tTt1.PNG

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:

 

tTt2.PNG

 
 
2 ACCEPTED SOLUTIONS

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

v-angzheng-msft
Community Support
Community Support

Hi, @AW1976NOVA 

 

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:

vangzhengmsft_0-1632711458184.png

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:

vangzhengmsft_1-1632711647225.png

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.

 

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

Hi, @AW1976NOVA 

 

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:

vangzhengmsft_0-1632711458184.png

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:

vangzhengmsft_1-1632711647225.png

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
Super User
Super User

Hi @AW1976NOVA 

 

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!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Thank you for the help so far!

 

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

 

Measure.PNG

 

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.

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.