Filter a RANKX result on a specific visual

[Stemar_Aubert]

Hello and happy new year.

I have a RANKX formula that gives out the rank of sales people based on specific KPIs.

A slicer on a page changes the KPI on which sales people are ranked.

IF (
            SELECTEDVALUE ( RankMethod[Flag] ) = 1,
            RANKX ( ALLNOBLANKROW ( [SALES REP] ), [KPI_0] ),
            IF (
                SELECTEDVALUE ( RankMethod[Flag] ) = 2,
                RANKX ( ALLNOBLANKROW ( [SALES REP] ), [KPI_1] ),
                IF (
                    SELECTEDVALUE ( RankMethod[Flag] ) = 3,
                    RANKX (
                        ALLNOBLANKROW ( [SALES REP] ),[KPI_2] ),
                    RANKX (
                        ALLNOBLANKROW ( [SALES REP] ),[KPI_3]
                    )

This is an extract of my formula (RankAll). It works for what I need, however I there is an issue with a specific visual.

 When I put the KPI_0 and another measure (KPI_0 vs Target) to build the Measure Absolute and Measure Deviation in the custom Scroller visual, I can't use my RANKX formula to filter on a specific Sales individual.

When looking at the scroller for the #2 individual, applying RankAll = 2 in PBI filters doesn't work. This might be caused by the fact that I use a product category in this visual, which breaks my Ranking Formula.

Is there a way to write the RANKX formula to work when I apply categories, or filter for only a specific Sales individual ?

I have tried multiple ways, including a formula that gives out the name (string) of the #x individual :

Rep 2 = FILTER(VALUES([SALES REP]),[RankAll] =2)

However, it doesn't work.

What works, albeit not well, is when I put the SALES REP in the visual's filter field and then apply a BOTTOM N + RankAll. However, that creates another issue since it only works for the BOTTOM 1, as BOTTOM 2 takes two individual worth of data, BOTTOM 3 takes three, etc.

Thanks for your ideas !

[parry2k]

@Stemar_Aubert how your tables are connected? Seems like you have to remove filter from product category. To simply test it, put a table visual, add sales person and product category and rank measure in it and see if your rank is showing expected result, if not that is your problem and need to be fixed.

[Stemar_Aubert]

Hello @parry2k ,

You are correct. The table doesn't yield what I need and product categories mess up my RankAll formula.

I created another Rank formula which "holds", meaning it will always give the same rank to an individual, even on a table showing product categories.

TEST =
CALCULATE (
    RANKX (
        ALLNOBLANKROW ( Reps[INDUSTRY REP] ),
        [KPI_4]
    ),
    ALLSELECTED ( Machine[PRODUCT FAMILY] )
)

However it still doesn't work when I use it as a filter in the custom visual and seems broken. Filtering on "TEST is 2" for exemple, the visual displays nothing at all.

[parry2k]

@Stemar_Aubert IN which visual it is not working? How you are filtering it? Can you share that info please?

[Stemar_Aubert]

I am using this custom visual :

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381018?tab=Overview

Filtering by Sales Representative Name works
Using the TEST measure doesn't work.

As you can see, if I filter using the Sales rep name, it works (data is scrolling correctly).

But when I use the TEST measure to filter, it doesn't give anything.

Thanks !

[Stemar_Aubert · Accepted solution]

Ok so I solved it.

I can't pass the rankx formula in the filter fields, as it breaks. I couldn't also use it to generate a new DAX table, because I have report-wide filters, and it would not capture them.

So what I did was, create 3 measures that yields the name of Sales individual 1,2 and 3 each. Then pass the Sales individual name as filter on visual, select TOP n of this measure. Works perfectly.

Not pretty, but I got what I needed.