Forum Discussion
RANKX Function considering slicer value
Hi sande_ch,
A bridge table helps resolve many-to-many relationships and avoids filter ambiguity by creating clear one-to-many connections between related tables.
RANKX), functions like ALL() or ALLSELECTED() can remove filters and return all category values, even if no data exists for the current selection (e.g., company or accident type).This leads to "empty" categories appearing in visuals
Rank only those activities where the number of accidents is greater than zero for the selected company and type of accident.
Regards,
Vinay Pabbu
HI Anonymous
Thanks for your reply!
Can you give me an example of a bridge table? I don't know exactly what you mean by that.
If I use the number of accidents (total_id_accident) as the sort order, it doesn't show me the “empty” categories:
But as soon as I choose the ranking as sort order, it shows them:
Do you have any tips for me on how to customize the ranking formula so that it only shows activities with accidents in the selected companies and accident types?
Kind regards
Hi sande_ch,
A bridge table helps resolve many-to-many relationships and avoids filter ambiguity by creating clear one-to-many connections between related tables.
RANKX), functions like ALL() or ALLSELECTED() can remove filters and return all category values, even if no data exists for the current selection (e.g., company or accident type).This leads to "empty" categories appearing in visuals
Rank only those activities where the number of accidents is greater than zero for the selected company and type of accident.
Regards,
Vinay Pabbu
Hi Anonymous
Thank you very much for your help. Adjusting my formula for "rank_value" has solved the problem:
rank_value = IF([total_id_accident]>0, VALUE(CALCULATE([rank_order], ALLSELECTED('Cases'[activity_subcat]))&[rank_order]), BLANK())
The empty categories are no longer displayed:
Thank you!
Kind regards
Sandra