RANKX with Filter doesn't work

[elsteshepardo]

Hi all

I'm currently struggling to work out a ranking issue

I want to calculate the ranking score - but I only want to calculate it for 'open' scores.

I have 2 tables - 1 table contains the scores, the other is the Primary table for the Project - with a status column

I have this measure which works. However, the ranking applies to ALL rows and the filter only applies to the resulting table - so I only see 'open' requests, but the rankings aren't correct

Ranking = 
VAR AssessmentRank = 
RANKX(
    ALL('Request Assessment'),
    CALCULATE(SUM('Request Assessment'[TotalMinusEffort])),,DESC,Dense)

VAR Result = 
IF(MAX('Request Assessment'[TotalMinusEffort]) <> 0 && MAX('DS Requests'[Stage]) <> "Complete" , AssessmentRank)


RETURN
Result

I guess that's because the ranking isn't filtered. So I've tried to Google fixes, and I've tried this

RankingV2 = 
 
VAR AssessmentRank = 
RANKX(FILTER(
    ALL('Request Assessment'),MAX('DS Requests'[Stage]) <> "Complete"),
    CALCULATE(SUM('Request Assessment'[TotalMinusEffort])),,DESC,Dense)


VAR Result = 
IF(MAX('Request Assessment'[TotalMinusEffort]) <> 0 , AssessmentRank)


RETURN
Result

This seems to rank everything (including the ones that are complete) - and those that are Complete are ranked with a value of 1

How can I filter the ranking correctly?

Thanks

[Anonymous · Accepted solution]

Hi ALL,
Firstly  bhanu_gautam thank you for your solution!
And @elsteshepardo ,For this problem of yours, we have tried to improve the code a little bit to produce the desired effect you want, and we hope this solution can provide you with ideas!

RankingFiltered = 
VAR AssessmentRank = 
    RANKX(
        FILTER(
            ALL('Request Assessment'),  
            'Request Assessment'[TotalMinusEffort] > 0
            && RELATED('DS Requests'[Stage]) = "Open"  
        ),
        CALCULATE(SUM('Request Assessment'[TotalMinusEffort])),
        , DESC, DENSE
    )

RETURN
IF(
    MAX('Request Assessment'[TotalMinusEffort]) > 0 && MAX('DS Requests'[Stage]) = "Open", 
    AssessmentRank,
    BLANK()
)

If you have more recent questions, you can contact me at any time, I will reply to you as soon as I receive your message, I look forward to hearing from you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[elsteshepardo]

Thank you so much! That's fixed it - I'll go and look at what RELATED is to work out why this has worked

[bhanu_gautam]

@elsteshepardo , Try using 

RankingV2 =
VAR AssessmentRank =
RANKX(
FILTER(
ALL('Request Assessment'),
'DS Requests'[Stage] <> "Complete"
),
CALCULATE(SUM('Request Assessment'[TotalMinusEffort])),
,
DESC,
DENSE
)

VAR Result =
IF(
MAX('Request Assessment'[TotalMinusEffort]) <> 0 &&
MAX('DS Requests'[Stage]) <> "Complete",
AssessmentRank
)

RETURN
Result