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 ( DISTINCTCOUNT(changedetails[changeid] ) ) )

I'm pretty new to PowerBI, so any help would be appreciated!

 

Kind Regards,

Dayna

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

5 Replies

  • 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.