Forum Discussion
RANKX Function considering slicer value
Hi there
I am new here and hope to find help.
Simply put, I have the following data table. It is a list of accidents (id_accident). For each accident I also have the ID of the company in which the accident victim works (id_company). For each accident, I also have the details of the activity involved in the accident as a category (activity_cat) and as a subcategory (activity_subcat ).
I would like to create a bar chart in Power BI with the number of id_accident by activity category and activity subcategory. I want to be able to select the desired companies using a slicer (filter on id_company). I want to create a ranking so that the activity categories are sorted by frequency in the bar chart and the subcategories are also sorted by frequency within these categories. I use the RANKX function for this.
The aim is that, depending on the selection of companies (filter by id_company), it reorganizes the ranking and then only considers activities for the ranking (and then also displays them in the graphic) that actually occur at the selected company. My problem now is that the ALLSELECTED function within the RANKX function does not work. Presumably because it refers to the activities and not to the companies.
My formula looks like this:
act_cat_rank = RANKX(ALLSELECTED('table'[activity_cat]), [total_id_accident],, DESC, Dense)
and:
act_subcat_rank = RANKX(ALLSELECTED('table'[activity_subcat), [total_id_accident],, DESC, Dense)
"total_id_accident" is a measure with the following formula: total_id_accident = COUNT('table'[id_accident])
Summarized again: My problem is that all activities are taken into account for the ranking, regardless of whether they occur at the selected company/companies. Is there a way I can solve this?
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
14 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi sande_ch please check this
Subcategory Rank =VAR accidentcount = [Accident Count]VAR ActivityInCategory =CALCULATETABLE (VALUES ( 'accidents'[activity_subcat] ),ALLSELECTED('accidents'),VALUES ( 'accidents'[activity_cat] ))VAR subactivityRank =IF (ISINSCOPE ( 'accidents'[activity_subcat] ),RANKX (ActivityInCategory,[Accident Count],,DESC,DENSE))VAR Result =IF (NOT ISBLANK (accidentcount),subactivityRank)RETURNResultHi there
While creating a sample dataset and a sample report, I realized that my problem has nothing to do with the RANKX function. This works as long as I make the slicer setting on variables of the same dataset.
To understand: I have four tables in my report.
Cases: A list of accidents and the associated characteristics such as activity, but also the type of accident (Occupational accident or Leisure accident) and the company where the accident victim works.
Cases 2: I have not created this table in my example report, as it is not relevant for this case. Here I have different information depending on the type of accident and company (e.g. for the industry comparison).
Company: Here are ID_Company, name of the company and, depending on the type of accident, other information (not relevant here). I therefore have two entries here for one company.
Type of Accident: Dimension table for the type of accidentI now have three problems.
Problem 1:
As long as I make the selection for the Company and the type of accident on the variables of the "Cases" table in the slicer, my ranking works (with the exception of problem 2 described below). However, I would like our customer to only have to select the company and the type of accident once in the report and this setting then applies to all graphics. Therefore, I have made the selection of the type of accident on the dimension table “Type of Accident” and the selection of the company on the variable “Company Name” of the table “Company”. I have linked the tables in the data model via the variable “ID_ToA” (ID of the type of accident) or via a combined variable of company and type of accident. With this type of selection, my ranking does not work, as I always get all entries from activity_cat and activity_subcat, regardless of whether the selected company had accidents in this activity.Problem 2:
For activity categories that have more than 10 subcategories, the ranking does not quite work. Since I link the ranking of the top category with the ranking of the subcategory for the rank_value, I get a three-digit rank for the subcategories that have a rank of 10 or higher, which means that these activities are added at the very end of the sorting (see in my sample report the page "how it works" for the company 10 (id_company = 10).
Problem 3:
The sorting order in my bar chart should be descending by rank_value and the view should be set to the lowest data level (activity_cat and activity_subcat should be displayed hierarchically). Every time I change the slicer setting (e.g. select a different company), the bar chart only shows me the top data level (only the activity_cat), sorted by activity_cat. Is it possible to fix the sorting order and also the data level to be displayed?
In my sample report I have a page called "My use case" which shows the problem 1. On the page "how it works" you on the example of the company 8 how my bar chart should look like. The selection of Company 10 shows my problem 2.
You can find my sample report under the following link: https://drive.google.com/file/d/1PAX9oQlYzG7HwRFkpXSPE9-77pMSaTmv/view?usp=sharing
Thank you!
