topic Re: Ranking issue when filtering in DAX Commands and Tips

Ranking issue when filtering

NB_Data — Mon, 18 Apr 2022 22:05:44 GMT

Hi,

I'm running into an issue when using the RANKX function within a table, and then filtering that table. Unfiltered, it works great, no issues with the actual ranked output field/column. But then if I filter the table for a particular hospital/institution it returns a completely wonky rank. I'm also having the rank for a particular hospital displayed in a card and it of course returns the same errant rank number.

[I've removed the hospital/institution names from the images below]

Unfiltered:

Filtered for that one hospital/institution above, that showed a rank of #8, it now shows #5, and the "% of Hosp Discharges" and "Sum Hosp %" also changed:

The measure for "DischargeRank Hosp.":

Appreciate the help in advance! This one has had me stumped!

Re: Ranking issue when filtering

amitchandak — Tue, 19 Apr 2022 02:33:42 GMT

NB_Data , Sum of discharge should not change with the filter for hospital/institution

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Re: Ranking issue when filtering

NB_Data — Tue, 19 Apr 2022 12:53:05 GMT

Hi amitchandak , unfortunately I can't easily create a de-identified dummy pbix to work with. The first screenshot is in table/matrix format, with institution and discharges being fields from the dataset, and the others being measures.

I can't be the only one that's experienced this table/matrix filtering issue.

Re: Ranking issue when filtering

johnt75 — Tue, 19 Apr 2022 13:16:52 GMT

Use Performance Analyzer to get the queries used both with and without filtering for an individual institution and compare them both in DAX Studio. It may be that some additional columns are being filtered beyond what you explicitly specify, e.g. a sort by column.

Once you know which columns are being filtered you may be able to alter your measures to factor that in.

Re: Ranking issue when filtering

v-xiaotang — Wed, 04 May 2022 05:42:52 GMT

Hi NB_Data

Thanks for reaching out to us.

You can try to replace all() with allselected(), as there is no sample data to test, not sure if it works, just try it.

Best Regards,

Community Support Team _Tang

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