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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

RANKX for a filtered subset

HI guys

 

Help needed! It is a very simple task that i need to achieve but my formula does not work, for unknown reasons.

 

I have a number of locations and need to filter for the top 10 locations with the most injuries but only for locations with more than a certain amount of actual worked hours (e.g. 30'000).

 

My approach is to first filter the LOCATION Dim Table with FILTER() and then do the RANKX by Injury. 

Here's my Formula so far:

Rank = 

RANKX(
    FILTER(ALL(DM_VW_LOCATION);[Actual Worked Hours]>=30000);
    CALCULATE([Total Injuries Rate];ALLEXCEPT(DM_VW_LOCATION;DM_VW_LOCATION[SiteCode]));;DESC;Dense)

 

Unfortunately the Measure still ranks locations below the 30'000 worked hours and provides very strange results:

airfreighter_0-1597647610801.png

There's no filter on that Matrix visualization yet, but my goal is to filter for Rank<=10 so that it only shows the top 10.

Any help? 

4 REPLIES 4
Anonymous
Not applicable

Hi @amitchandak 
Thanks for the link. I've gone through it but still don't know where things are going south. The filtering for the subset of locations does not seem to work.

Anonymous
Not applicable

did you get the solution?

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors