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 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 accident
I 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!