RANKX in Charts with Legend filtered

[Geroo]

Hi

Apologies if this is a simple fix but I cant seem to figure this out.

I have a table 'SOR' that lists tasks assigned to individuals.  I want to create a stacked chart to show the top 10 individuals with assigned tasks with various status displayed in the stack.  I need to exclude the completed tasks so only outstanding tasks are shown.

I have created a chart without the Status filter with a RANKX measure and this works fine

However I run into trouble when adding the Status column as the Legend as this trashes the RANKX function:

I have tried various things to resolve this such as placing a filter on the RANX function:

Top Responsible = RANKX(ALL(SOR[Person Responsible] ), CALCULATE(count(SOR[Person Responsible]),FILTER(SOR,SOR[Status] <> "Completed")))

Or this: 

Top Responsible = RANKX(FILTER(SOR,SOR[Person Responsible] <> "Completed") ), CALCULATE(count(SOR[Person Responsible])))

I have also tried using a page / visual filter for Status but I still cannot seem to show the Top 10 once I start looking at incorporating the Status field 😞

Any help appreciated, i guess its something simple (hopefully)

[SQLbyoBI · Accepted solution]

The problem is your ranking calculation is being evaluated within the context of the status (applied via the legend). You need to remove that filter inside the ranking calculation.

Top Responsible =
RANKX(
    ALL( SOR[Person Responsible] ),
    CALCULATE(
        COUNT( SOR[Person Responsible] ),
        FILTER(
            ALL(SOR[Status]),
            SOR[Status] <> "Completed"
        )
    )
)

The above is hard coded to disregard "Completed" status. You could make this more dynamic and apply that filter via the filter pane and use ALLSELECTED...

Top Responsible =
RANKX(
    ALL( SOR[Person Responsible] ),
    CALCULATE(
        COUNT( SOR[Person Responsible] ),
        ALLSELECTED(SOR[Status])
    )
)

 ...this would allow users to choose which statuses they want to disregard. 

[MonikaeM]

This was working for me... till the moment i added "Sort by Column" in Model View  

what if i would like to show "status" options in specific order ?

What i should do to make it work?

[Geroo]

Thank you, that has done the trick, you are a legend!!! (pun intended 😉 )