Forum Discussion
Post ProdigyField Parameter Rank On The Fly
Hi,
I want to be able to supply a rank number to my tablix on the fly, against a field parameter measure.
My measure is just returning 1 for all, I didn't know if there was a better way .
Thanks
Rank by Parameter =
RANKX(
ALL('Data'[Category]),
SELECTEDMEASURE(),
,
DESC,
DENSE
)
FieldParameter = {
("Amount", NAMEOF('Measure Table'[Total Amount]), 0),
("Usage", NAMEOF('Measure Table'[Total Usage]), 1)
}
Expected:
HI lherbert501
You are correct. A field parameter in Power BI is essentially a reference (a pointer) to a measure, so you cannot directly use it as the basis for a ranking calculation. The ranking must be done on the actual measures, and the result is then returned conditionally based on the parameter selection.
For example:
Rank_ =
VAR selected_ =
SELECTEDVALUE ( 'Measures for analysis'[Measures for analysis Order] )
VAR rankSales =
RANKX ( ALLSELECTED ( Products[Category] ), [Total sales (K/$)], , DESC )
VAR rankProfits =
RANKX ( ALLSELECTED ( Products[Category] ), [Total profits (K/$)], , DESC )
VAR rankProfits_percent =
RANKX ( ALLSELECTED ( Products[Category] ), [profits _ %], , DESC )
RETURN
SWITCH (
selected_,
0, rankSales,
1, rankProfits,
2, rankProfits_percent
)The output will be controlled by the parameter selected by the user.
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
- Ritaf1983
Super User
HI lherbert501
You are correct. A field parameter in Power BI is essentially a reference (a pointer) to a measure, so you cannot directly use it as the basis for a ranking calculation. The ranking must be done on the actual measures, and the result is then returned conditionally based on the parameter selection.
For example:
Rank_ =
VAR selected_ =
SELECTEDVALUE ( 'Measures for analysis'[Measures for analysis Order] )
VAR rankSales =
RANKX ( ALLSELECTED ( Products[Category] ), [Total sales (K/$)], , DESC )
VAR rankProfits =
RANKX ( ALLSELECTED ( Products[Category] ), [Total profits (K/$)], , DESC )
VAR rankProfits_percent =
RANKX ( ALLSELECTED ( Products[Category] ), [profits _ %], , DESC )
RETURN
SWITCH (
selected_,
0, rankSales,
1, rankProfits,
2, rankProfits_percent
)The output will be controlled by the parameter selected by the user.
The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
lherbert501thankyou Ritaf1983 this is really helpful 🙂
- Ritaf1983
Hapy to help 🙂
- Shahid12523
Community Champion
Rank by Parameter =
RANKX (
ALLSELECTED ( 'Data'[Category] ),
CALCULATE ( SELECTEDMEASURE() ),
,
DESC,
DENSE
)
This way, your rank updates on the fly based on the field parameter (Amount or Usage).
