Forum Discussion
Helper IWhy does sorting by rankx-function and measures work in desktop but not in Power BI Service?
Hi there
I use Power BI to create a report on accident statistics. Among other things, I use a bar chart to display the accident statistics by activity at the time of the accident. I use two activity hierarchies for this (categories and subcategories):
For sorting, I now use a complex logic based on rankx-function and multiple measures. This is because I want to sort the activity categories by frequency, and within the activity categories, the subcategories should also be sorted by frequency. I also have a Top N logic. The TopN selection controls how many subcategories are displayed within a category. All others are then grouped under “Others.”
This works perfectly in the desktop app. However, it doesn’t work reliably in the Service. Depending on the slicer selection (I use slicers to select the company of interest, the accident year(s) and the TopN), the sorting gets completely messed up. There’s no pattern, though; it seems to work randomly sometimes and not others.
While developing the report, I often read that the rankx-function is very sensitive. However, it does work in the desktop app. Does anyone have any idea why this causes problems in the Service but not in the desktop app, and how I can work around this issue?
At this link https://drive.google.com/file/d/1GaItIH-o09MNp5ZsidEahfPOkKDbGAqY/view?usp=drive_link, you'll find a sample report with sample data that follows the same logic. I've simplified it slightly by limiting the sample report to a single language. The original report is trilingual and therefore somewhat more complex.
Thank you for the support
Sandra
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.
7 Replies
- collinq
Super User
HI 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
sande_chHi
No, unfortunately it did not solve my problem. But thank you for the support!
Sandra
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.
