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

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

Hi Dayna,

 

Which column is containing blank value, [FullName] or [changid]? Could you share some sample data and clarify more details about your requirement and issue?

 

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