topic Re: RANKX for a filtered subset in DAX Commands and Tips

RANKX for a filtered subset

Anonymous — Mon, 17 Aug 2020 07:01:21 GMT

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:

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?

Re: RANKX for a filtered subset

amitchandak — Mon, 17 Aug 2020 07:33:24 GMT

Anonymous , for Rank refer these

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures // check category and sub category rank here
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Re: RANKX for a filtered subset

Anonymous — Mon, 17 Aug 2020 07:53:47 GMT

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.

Re: RANKX for a filtered subset

amitchandak — Mon, 17 Aug 2020 08:07:53 GMT

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

Re: RANKX for a filtered subset

Anonymous — Tue, 19 Oct 2021 14:13:37 GMT

did you get the solution?