how do I get my rank column to refresh as I filter my other data columns?

[Anonymous]

Hello PowerBI community -- I am looking for a solution on how to filter my table on the REPORT tab (ie I want to avoid using the "Transform Data" function) and have my rank column on my report tab update as I make filter adjustments.

Right now, for example, when I filter out specific products from my product column, the corresponding rank column does not update along with it.

Visual example:

PRODUCT COLUMN	RANK COLUMN
apples	1
oranges	2

What I want to happen is if I filter out "apples" from my PRODUCT COLUMN, I want the RANK COLUMN to show "oranges" as rank #1. At the moment, when I filter out "apples", the rank column shifts all my data up, but oranges and the data that follows is stuck with it's old rank

EDIT: I also want to know if I can filter my product column and have other columns dynamically update/refresh as well, such as Cumulative %, Market Share, and so on -- things that will require updates if the input data shifts.

Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[VALUE])),,DESC,Dense)

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

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

[mariussve1]

Long story short, if you have the rank as calculated column this need to be changes to a measure. Calculated columns will only change when model is refreshed, and not be affected by filters. Measures on the other hand is calculated on the fly and can therefor be filtered and affected real time on filters in the report.

If your already using measure to rank, never mind what I just wrote 😉

Br

Marius

[sevenhills]

Since we dont have your DAX or other columns info, little tough.

Rank =
RANKX (
    FILTER ( ALLSELECTED ( tbl ), tbl[Product] = MAX ( tbl[Product] ) ),
    [_maxPurchaseDate_Or_SumSalesAmount_Or_CountSomethingelse],
    ,
    ASC
)

Rank 2 =
RANKX (
    ALLSELECTED ( tbl ),
    [_maxPurchaseDate_Or_SumSalesAmount_Or_CountSomethingelse],
    ,
    ASC
)

Check this article to see if it helps

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale