Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to Solution.
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)
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.
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)
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.
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
59 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |