Forum Discussion

Dayna's avatar
Dayna
Helper V
7 years ago
Solved

Filter null values within Rank

Hi All,   Can you help, please? I'm trying to filter account the blank record from the following rank calculation: ChangeManagerRank = RANKX ( ALLSELECTED ( sduser_cm[FullName] ), CALCULATE ( DIS...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Hi Dayna,

     

    You should filter the blank value first in your measure, try modifying your measure like pattern below:

    ChangeManagerRank =
RANKX (
    ALLSELECTED ( sduser_cm[FullName] ),
    CALCULATE (
        DISTINCTCOUNT ( changedetails[changeid] ),
        FILTER ( changedetails, changedetails[changeid] <> BLANK () )
    )
)

    Regards,

    Jimmy Tao

Dayna's avatar
Dayna
Helper V
7 years ago

Hi v-yuta-msft,

 

It's the [FullName] that is blank, the return of my rank looks like this:

FullName        Rank

                        1

Joe Bloggs       2

Sarah Smith     3

 

The changeid is a count of all changes in a system, if the status = standard, then there's no person assigned, whereas the rest do. Really, this needs to be a count where status is not null.

 

Many thanks,

Dayna

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

Dayna Could you please post what is your sample input and your expected output. I appreciate that you already posted some sample data, but to be more clear and to avoid any confusion.

  • Dayna's avatar
    Dayna
    Helper V
    7 years ago

    Of course, this is what I'm getting:

    FullName        Rank

                            1

    Joe Bloggs       2

    Sarah Smith     3

     

    And this is what I need:

    FullName        Rank

    Joe Bloggs       1

    Sarah Smith     2

    • v-yuta-msft's avatar
      v-yuta-msft
      Community Support
      7 years ago

      Hi Dayna,

       

      You should filter the blank value first in your measure, try modifying your measure like pattern below:

      ChangeManagerRank =
RANKX (
    ALLSELECTED ( sduser_cm[FullName] ),
    CALCULATE (
        DISTINCTCOUNT ( changedetails[changeid] ),
        FILTER ( changedetails, changedetails[changeid] <> BLANK () )
    )
)

      Regards,

      Jimmy Tao