Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Rankx with Calculate

There is a few  Rankx threads out there but not for my easy situation. I have a DIM Table called  'Location' and a measure called Groups from seperate fact table.

I can rank my facilities no problem with this 

Rank =
RANKX(ALLNOBLANKROW('Location Mapping'[Facility]),[GROUPS])
 
However I want to exclude facilties from being ranked where column [Exclude] =Y in the Facilties. 
Idealy i thought this would work but the ALL still is in there and they are included.
calculate(RANKX(ALLEXCEPT('Location','Location'[Facility]),[Groups]),'Location'[Exclude]="Y")
Need Help

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    Hi Anonymous ,

     

    Assuming this is a calculated column and with just the information avialable, try this.  before ranking happens.

    =
RANKX (
    //filter the table before ranking an item
    FILTER (
        FILTER ( 'Location', 'Location'[Facility] = EARLIER ( 'Location'[Facility] ) ),
        'Location'[Exclude] = "Y"
    ),
    [Groups]
)

     

    If the  facility to be excluded still returns  a value, you can add a conditional column so the ranking is null

    =
IF (
    'Location'[Exclude] = "Y",
    RANKX (
        //filter the table before ranking an item
        FILTER (
            FILTER ( 'Location', 'Location'[Facility] = EARLIER ( 'Location'[Facility] ) ),
            'Location'[Exclude] = "Y"
        ),
        [Groups]
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I am doing the Rankx as a measure inside a MAtrix visual. I have another rank measure that does not exclude so thats why i cant just filter them out in th evisual pain either.

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        3 years ago

        Please provide sample data. It is easier for anyone who wants  to help with something to work on than just use the imagination.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Does this measure work?

    = RANKX(FILTER(ALLNOBLANKROW('Location Mapping'[Facility]),'Location'[Exclude]<>"Y"),[GROUPS])