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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
singhv18
New Member

RANK/RANKX not working when want to see Top/Bottom values based on selection

Dear All,

 

I'm trying to write Rank/RankX function to give me Top5/Bottom5 values based on selection. However, this is not working for me.

 

singhv18_0-1733311684999.png

Above is the output  I need with the slicers it will interact with.

Problems:
I get the overall rank correct, but when I apply slicers (4 slicers on right side of image) there are 2 issues -
1. Rank starts from 2 (not 1) or,
2. Ranks for some values are skipped

All columns are from same table. 


Current DAX:

# Rank =

VAR _BottomMargin = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [margin], ASC))


VAR _TopMargin = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [margin], DESC))

 

VAR _BottomRevenue = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [Revenue], ASC))

VAR _TopRevenue = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [Revenue], DESC))

 

VAR _Result = IF(SELECTEDVALUE(Selection[Selection]) = "Margin Top",_TopMargin,

             IF(SELECTEDVALUE(Selection[Selection]) = "Margin Bottom", _BottomMargin,

             IF(SELECTEDVALUE(Selection[Selection]) = "Revenue Top",_TopRevenue,

             IF(SELECTEDVALUE(Selection[Selection]) = "Revenue Bottom", _BottomRevenue))))

RETURN

_Result

1 ACCEPTED SOLUTION
v-denglli-msft
Community Support
Community Support

Thanks for the reply from bhanu_gautam, please allow me to provide another insight.
Hi @singhv18 ,

Please try the following measure.

# Rank = 
VAR _BottomMargin = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Margin])),,DESC)
VAR _TopMargin = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Margin])),,ASC)
VAR _BottomRevenue = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Revenue])),,DESC)
VAR _TopRevenue = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Revenue])),,ASC)
RETURN
SWITCH(
    SELECTEDVALUE(Selection[Selection]),
    "Margin Bottom",_BottomMargin,
    "Margin Top",_TopMargin,
    "Revenue Bottom",_BottomRevenue,
    "Revenue Top",_TopRevenue
)


The final result is as follows.

vdengllimsft_0-1733735839267.png


Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

3 REPLIES 3
v-denglli-msft
Community Support
Community Support

Thanks for the reply from bhanu_gautam, please allow me to provide another insight.
Hi @singhv18 ,

Please try the following measure.

# Rank = 
VAR _BottomMargin = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Margin])),,DESC)
VAR _TopMargin = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Margin])),,ASC)
VAR _BottomRevenue = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Revenue])),,DESC)
VAR _TopRevenue = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Revenue])),,ASC)
RETURN
SWITCH(
    SELECTEDVALUE(Selection[Selection]),
    "Margin Bottom",_BottomMargin,
    "Margin Top",_TopMargin,
    "Revenue Bottom",_BottomRevenue,
    "Revenue Top",_TopRevenue
)


The final result is as follows.

vdengllimsft_0-1733735839267.png


Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

bhanu_gautam
Super User
Super User

@singhv18 , Try using 

 

# Rank =
VAR _SelectedValue = SELECTEDVALUE(Selection[Selection])
VAR _OrderByColumn =
SWITCH(
_SelectedValue,
"Margin Top", [margin],
"Margin Bottom", [margin],
"Revenue Top", [Revenue],
"Revenue Bottom", [Revenue]
)
VAR _Order =
SWITCH(
_SelectedValue,
"Margin Top", DESC,
"Margin Bottom", ASC,
"Revenue Top", DESC,
"Revenue Bottom", ASC
)
VAR _Rank =
RANKX(
ALLSELECTED('YourTable'),
_OrderByColumn,
,
_Order,
DENSE
)
RETURN
_Rank




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






@bhanu_gautam 

Thank you for you inputs!

Getting below error, can you pls help with this.

singhv18_0-1733324081648.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.