Forum Discussion
Helper IWhy does sorting by rankx-function and measures work in desktop but not in Power BI Service?
Hi sande_ch ,
Thanks for the update and for checking it.In Power BI, measures such as those created using RANKX are evaluated based on the current filter context. Because of this behavior, their results can change depending on how the visual is filtered or interacted with.
For sorting behavior, Power BI provides the Sort by column option, which allows a field to be sorted based on another column defined in the model. This enables a consistent sort order defined at the model level.
If the issue persists, sharing a simplified version of the report or visual setup would help in understanding the exact scenario.
Thank you.
Super UserHI sande_ch ,
HI @sande_ch ,
Service and Desktop use different methods to execute queries and so you can sometimes get "errors" in the Service when it worked fine in Desktop. The biggest difference for ranking is that the Service does it in parallel while the Desktop does it sequentially. Which means that ties in the Service can be different than the Desktop.
Specifically, I found this:
RANKX() does not guarantee stable ordering when:
two or more rows have the same value
the expression depends on measures
the table parameter is dynamically filtered
slicers change cardinality during evaluation
Desktop often appears stable because:
evaluation order is consistent
caching masks the issue
Service exposes the issue because:
parts of the query are recomputed independently
SO, the best way I found to resolve this specific issue is to replace RANKX() with somewhat static order. In this case, create category frequency then subcategory frequency and generate numeric sort keys and then use sort by column on that.
Something like this should work:
Category Sort Order :=
CALCULATE (
SUM ( 'Fact'[Accident Count] ),
ALLEXCEPT ( Activity, Activity[Category] )
)
Then Set Activity (Category) - Sort By - Category Sort Order
