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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.