Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Stemar_Aubert
Resolver I
Resolver I

Run RANKX on partial data, and keep ranking in filtered tables visuals.

Hello everyone,

 

I have a question regarding RANKX(). My report consists of a leaderboard ranking different Districts based on a calculated measure (YoY Growth in %). The tricky thing is that I must "place" each District under its respective Region. You have North America, South America, etc. Also, the top three Districts have their own box at the top.

 

2019-10-22 08-14-59_Window.png

 

Now however,  my solution creates the following issues:

 

2019-10-22 08-13-44_Window.png

 

On this table, you see the results of my RANKX() formula, which doesn't work.

Districts with "OTHER" in their names are filtered out from the ranking (but must be kept in the data model as their values add up to Regional level), and I think they are the cause of the problem.

 

Currently, my RANKX() formula is this one :

Rank  = if(hasonevalue(Local[DISTRICT]),

calculate(rankx(FILTER(all(Local),Local[Custom]=0),FPins[PINS YTD YoY Δ]),

ALLEXCEPT(Local,Local[DISTRICT])),

"-")

Local is a table with District, Region and Global Region Data.

The Local[Custom]=0 is a way for me to flag "OTHER" districts (they have the value 1 so I can exclude them).

FPins in the big table with historical data, and PINS YTD YoY Δ is a measure of YTD Growth.

I use the if statement in order to display "-" when, on another page, somebody selects more than one District.

 

Currently, the model looks like so.

2019-10-22 08-27-16_Window.png

(The 3P part is an oddity that I must keep to calculate an aggregate number, but it has no link whatsoever in the Ranking. At the moment, the relations are inactive, but they are set to active when the model is running. I disabled them for testing at the moment.)

 

So in summary, my question is, what variation of RANKX() (or maybe EARLIER()) can I use in order to run a ranking on a sample of my Districts (exclusion of OTHERS), but also having the RANKX() stay valid when I filter out the Districts by Leader Boxes and Regional Boxes.

 

Thanks a lot for your help and for reading up to here.

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@Stemar_Aubert ,

 

You may modify your measure using dax below and check if it can meet your requirement:

Rank =
IF (
    HASONEVALUE ( Local[DISTRICT] ),
    RANKX (
        FILTER (
            Local,
            Local[Custom] = 0
                && Local[DDISTRICT] = EARLIER ( Local[DISTRICT] )
        ),
        FPins[PINS YTD YoY Δ]
    ),
    "-"
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

@Stemar_Aubert ,

 

You may modify your measure using dax below and check if it can meet your requirement:

Rank =
IF (
    HASONEVALUE ( Local[DISTRICT] ),
    RANKX (
        FILTER (
            Local,
            Local[Custom] = 0
                && Local[DDISTRICT] = EARLIER ( Local[DISTRICT] )
        ),
        FPins[PINS YTD YoY Δ]
    ),
    "-"
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.