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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
chloerosenberg
New Member

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

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 COLUMNRANK COLUMN
apples1
oranges2

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!

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

Hi @chloerosenberg ,

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)

vpollymsft_0-1667354688007.pngvpollymsft_1-1667354704073.png

 

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.

View solution in original post

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

Hi @chloerosenberg ,

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)

vpollymsft_0-1667354688007.pngvpollymsft_1-1667354704073.png

 

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
Solution Supplier
Solution Supplier

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
Super User
Super User

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 

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.