Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello, I have a data set that I am ranking each hierarchy in ascending order. The first level works correctly and ranks properly, the issue is that once I drill down to the next level rankx correctly ranks the subset but wants to start at the bottom of the entire population vs ranking the subset. I have to hardcode everything as well within measures as I am combining several metrics into one matrix that cannot all be filtered the same. I have tried several variations of the following code:
Results when drill down to IL:
I am just trying to figure out how to return 1-6 when drilled down. RANKX is properly ranking within the subset, just not using the filtered down count of offices for the ranking numbers. Any help is appreciated. Thanks
Hi @JJohns ,
According to your description, if you want to dynamically rank the results based on filtering, you can use the allselect function.
Keep the results of filtering in the external filtering context. The reference is as follows.
Rank_ =
IF (
HASONEVALUE ( DimProduct[Color] ),
RANKX ( ALLSELECTED ( DimProduct[Color] ), [Amount_] ),
BLANK ()
)
If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Henry I accidently accepted this as a solution but I am still having the same issue. I am attaching a sample test set that replicates my exact issue. The table has 3 columns State, Office, Amount. I created a measure: Amount_2 = CALCULATE(SUM(Amount)) so that I have a measure to rank. I then use the following Dax for my Rank measure:
Results when expanded to office:
Results I am expecting:
Table used:
State | Office | Amount |
SC | 100 | 39 |
SC | 101 | 29 |
SC | 102 | 42 |
SC | 103 | 45 |
SC | 104 | 76 |
SC | 105 | 95 |
SC | 106 | 92 |
SC | 107 | 87 |
IL | 200 | 77 |
IL | 201 | 3 |
IL | 202 | 50 |
IL | 203 | 67 |
IL | 204 | 25 |
IL | 205 | 71 |
IL | 206 | 58 |
IL | 207 | 72 |
IL | 208 | 88 |
IL | 209 | 78 |
IN | 300 | 37 |
IN | 301 | 80 |
IN | 302 | 39 |
IN | 303 | 4 |
IN | 304 | 82 |
IN | 305 | 60 |
IN | 306 | 59 |
" I have to hardcode everything as well within measures" not sure what you mean by that. seems mutually exclusive.
Note that if you use RANKX in measures you need to do a couple extra steps, and you need to stay aware of the filter context for each.
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
85 | |
82 | |
69 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |