Forum Discussion

sande_ch's avatar
sande_ch
Icon for Helper I rankHelper I
1 year ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

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/1447523

sande_ch's avatar
sande_ch
Icon for Helper I rankHelper I
1 year ago

Hi lbendlin 

I have sent a link to a sample pbix file. Please read my post below.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    I would recommend you refactor your data model before continuing.

     

     

    • sande_ch's avatar
      sande_ch
      Icon for Helper I rankHelper I
      1 year ago

      Hi 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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