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

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