Forum Discussion

BigSweets's avatar
BigSweets
Regular Visitor
3 years ago

Use slicer visual with multiple fields within RANKX formula - make it dynamic

I have a slicer visual with 4 fields (pictured below) that represent various levels of operation. I have a RANKX formala that uses the slicer but can't accomadate the various levels, only the OPCO level.  How do I change the below formula to adjust to whatever operation level is selected in the slicer visual? How do I make the RANKX measure more dynamic? I am trying to avoid duplicating these measures at the various levels.

Thank you in advance for your help.

Total Equip Damage Rank = RANKX(ALLSELECTED(OPCO[OPCO]),'Good Catch'[Total Equipment Damage],,ASC,Dense)

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

     

    whatever operation level is selected in the slicer visual

     

    Multiple levels can be "selected" at the same time.  Please be more specific.

  • BigSweets's avatar
    BigSweets
    Regular Visitor
    3 years ago

    Thank you for responding. I use the same slicer visual pictured/described above as a filter in my workbook with no issues. However, I can not use this same filter visual with the RANKX formula as it will not roll up/down as expected. My RANKX formula is specific to the OPCO level in my example above. I don't know how to modify it to add all 4 levels. I am not sure if I could make a nested if statement to achieve the desired outcome. Currently, I created a tab for each level and have  RANKX formulas specific to each level. I would like to have one tab that I can filter the slicer visual and the RANKX formula adjusts accordingly and the ranks are as exected.

      • BigSweets's avatar
        BigSweets
        Regular Visitor
        3 years ago

        Thank you. Hmmm... I don't think I have time to create fake data for all of the tables involved. I can't provide the actual data for security concerns. Basically, each field in the slicer visual comes from 2 lookup tables. The Manager (5 managers) and OPCO (9 different OPCOs) come from the OPCO lookup. The Supervisor (22 supervisors) and Garages (103 garages) come from the Garage Lookup.  
        I have 12 different RANKX measures in the data. I need to see all 12 measures and the corresponding RANKX measures at each of the 4 operating levels when selected. I assume I need to create a nested if RANKX measure that will be able to adjust accordingly when selected in the slicer visual. Otherwise, I will need to create 48 RANKX measures (12 X the 4 operation levels) and 4 different tabs to achieve this outcome. 

        Are you aware how to create a RANKX formula with all 4 of the operation levels in it like a nested if statement in Excel? If that is not possible then we don't need to go any futher.


        Total Equip Damage Rank = RANKX(ALLSELECTED(OPCO[OPCO]),'Good Catch'[Total Equipment Damage],,ASC,Dense)