Forum Discussion
Helper IRANKX 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
Helper IHi lbendlin
The problem is that I still have the second “Cases” table, which I would then also have to link to the “Type of accident” and “Companies” table and then I have ambiguous paths, which is why I cannot activate the relationship then. I don't want to (or can not) merge the two tables “Cases” and “Cases2” because they come from different data sources.
But suppose I were to revise my data model according to your suggestion. Would that solve my problem? If I continue to make the slicer selection based on variables in the “Type of accident” and “Companies” tables, I still have the problem that all categories are displayedwhen I use the ranking as the sorting order.
Hi sande_ch,
Using a bridge table will resolve ambiguous relationships issue.
Will all categories still be displayed when using ranking as the sorting order?
Even after fixing the relationships, Power BI may still show all activity_cat values because ALLSELECTED retains the full category list. To prevent this, modify the ranking formula to only rank activities that have accidents in the selected companies and type of accident
Regards,
Vinay Pabbu