Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
BigSweets
Regular Visitor

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)

slicer visual fields.png

5 REPLIES 5
BigSweets
Regular Visitor

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.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

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)

Sorry to hear that. Can't help in that case.

lbendlin
Super User
Super User

 

whatever operation level is selected in the slicer visual

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors