Filter null values within Rank

[Dayna]

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

[v-yuta-msft]

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]

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]

@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]

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 · Accepted solution]

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