Forum Discussion

elsteshepardo's avatar
elsteshepardo
Frequent Visitor
1 year ago
Solved

RANKX with Filter doesn't work

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

DAX
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      elsteshepardo
      Frequent Visitor
      1 year ago

      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's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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