Forum Discussion

Stemar_Aubert's avatar
Stemar_Aubert
Resolver I
6 years ago
Solved

Exclude items from RANKX evaluation and

Hello,   I am trying to rank the performance of my Districts based on their YoY Growth using RANKX().   Current formula, where Custom is a flag to exclude specific districts: if( ha...
Need Help
Tutorial Requests
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Stemar_Aubert 

     

    Sorry, my bad, FILTER can only accept one-two arguments, try the below instead.

    VAR _search = "OTHER"
VAR _tbl =
    CALCULATETABLE(
        FILTER(
            ALLSELECTED( Local[DISTRICT] ),
            SEARCH( _search, Local[DISTRICT], 1, 0 ) = 0
        ),
        Local[Custom] = 0
    )
RETURN
IF(
    SEARCH( _search, SELECTEDVALUE( Local[DISTRICT] ),  1, 0 ) = 0,
    RANKX( _tbl, [PINS YTD YoY Δ] ) 
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Stemar_Aubert 

 

Sorry, my bad, FILTER can only accept one-two arguments, try the below instead.

VAR _search = "OTHER"
VAR _tbl =
    CALCULATETABLE(
        FILTER(
            ALLSELECTED( Local[DISTRICT] ),
            SEARCH( _search, Local[DISTRICT], 1, 0 ) = 0
        ),
        Local[Custom] = 0
    )
RETURN
IF(
    SEARCH( _search, SELECTEDVALUE( Local[DISTRICT] ),  1, 0 ) = 0,
    RANKX( _tbl, [PINS YTD YoY Δ] ) 
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Stemar_Aubert's avatar
Stemar_Aubert
Resolver I
6 years ago

Thank you so much!

 

I have adjusted the formula by switching ALLSELECTED to ALL, because my tables must only show a specific region District.

 

The "OTHER" districts are removed and not included in the Ranking, which is perfect.

 

 

There is a blank District, which might be removed using a filter excluding the blanks districts. 

 

Just a final question. If I want the Rank to be a specific value (like "-") when more than one district is displayed, where should I put the if(hasonevalue() statement ?

 

Thanks again!

 

Edit: Nevermind, I had a discrepant value in another table creating the blank. Solved it.