Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
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
Solved! Go to Solution.
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.
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.
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.
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.
@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
Proud to be a Super User! |
|
User | Count |
---|---|
21 | |
14 | |
11 | |
8 | |
5 |
User | Count |
---|---|
24 | |
22 | |
20 | |
15 | |
10 |