topic Re: Rankx Not working as expected when data is filtered using slicers and filter pane in DAX Commands and Tips

Rankx Not working as expected when data is filtered using slicers and filter pane

Vivek26 — Sat, 04 Jan 2025 16:05:17 GMT

I am facing a challenge with Rankx implementation.

When I use slicers to filter certain dimensions the rankx starts behaving incorrectly

The dataset/Datamodel is very simple, We have 3 tables

Dimension table - Store( Storeid, District,Region)

Fact Table - Sales (SalesID,Storeid,SalesDate,Amount)

Calendar( For time Series analysis) -(Date,day,MonthName,Month,Quarter)

Store to Sales - One to many relationship on the basis of Storeid

Calendar to Sales - One to many relationship on the basis of Date

This is how my Store table looks like

StoreId District Region

Store1 Mumbai North

Store2 Nagpur East

Store3 Kochi North

Store4 Hyderabad West

Store5 Delhi North

Store6 Kanpur East

This is how my sales data looks like , They have a one to many relationship on the basis of StoreId.

ID StoreId SalesDate Amount

1 Store87 15-06-2020 41

2 Store99 10-07-2021 44

3 Store70 29-06-2018 39

4 Store31 05-04-2024 32

5 Store56 19-01-2023 12

6 Store25 11-02-2021 79

Now I want to rank on the basis of District and sales and I am using Region District StoreId in my slicer .

The output should look like this

District|Total Sales|RankBasedonSales

Delhi|10000|1

Kanpur|9000|2

Nagpur|8000|3

Here Total Sales = SUM(Sales[Amount])

when I write the below dax to calculate the rank, It works perfectly if I don't filter anything on my slicer.

Once I start filtering using slicer the dax breaks

RankBasedOnSales-Dimension =

RANKX(

ALLSELECTED(Store[District]),

[Total Sales]

)

To Test another approach I added a calculated column in District in my fact table using the related function

and I called it calculated District Then I modified the dax as below

rankbasedonsales =

RANKX(

ALLSELECTED(Sales[Calculated District]),

[Total Sales]

)

after implementing this the dax works perfectly( for this I will use calculated district in my visual along with sales and rank)

I don't want to add a column for obvious reasons, I am sure we can do it without using the calculated column .

Can you please help me here

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

Greg_Deckler — Sat, 04 Jan 2025 17:13:34 GMT

Vivek26 What do you mean by "DAX breaks"? You get an error, you get incorrect results, ... ? You might try RANK instead of RANKX

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

Vivek26 — Sat, 04 Jan 2025 18:11:38 GMT

Greg_Deckler

Hi Greg , Thanks for replying.

The dax doesn't break but the ranking order gets messed up

For example post filtering , Kanpur may get the 3rd Rank while it should be 2nd

District|Total Sales|RankBasedonSales

Delhi|10000|1

Kanpur|9000|3

Nagpur|8000|2

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

powerbiexpert22 — Sun, 05 Jan 2025 07:21:33 GMT

Hi Vivek26,

please see below pbix for your reference , the rankx function is showing correct results

you will have to create measure first for sales amount and then use it inside rank function ( do not use sum(col) directly in your rank measure

https://drive.google.com/file/d/1TmjidSpb2KktCGYUkfRDQlnc_nAWsL3L/view?usp=drive_link

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

Vivek26 — Sun, 05 Jan 2025 07:48:53 GMT

powerbiexpert22 . Thanks for replying.

I used your pbix but instead of having 3 different slicers . I used a single one and I started getting the same issue.

My Apoligies I should have clarified my usage of slicer

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

powerbiexpert22 — Sun, 05 Jan 2025 09:05:44 GMT

Hi Vivek26 ,

try to use below

rank =

RANKX(ALLSELECTED(store[region],store[district],store[storeid]),[Total Sales],,DESC,Dense)

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

Vivek26 — Sun, 05 Jan 2025 21:01:12 GMT

powerbiexpert22

If you filter a specific store in a district, we will again start getting rank 1 for everything . That is my main point of concern.

In a district we want to exclude certain stores .

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

powerbiexpert22 — Mon, 06 Jan 2025 00:30:48 GMT

Hi Vivek26 ,

can you let me know in which scenario in my pbix file is giving all ones?

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

Anonymous — Mon, 06 Jan 2025 08:44:45 GMT

Hi Vivek26

Please check this measure. Although it is not concise enough, it seems to meet your requirements.

rank = var currentDistrict = SELECTEDVALUE(Store[District]) var sumTable = SUMMARIZE(ALLSELECTED(Store),Store[Region],Store[District],"Total_Amount",[Total Sales]) var rankTable = ADDCOLUMNS(sumTable,"Rank_Value",RANK(DENSE,sumTable,ORDERBY([Total_Amount],DESC))) return MAXX(FILTER(rankTable,[District] = currentDistrict),[Rank_Value])

I struggled a lot but haven't figured out a method with RANKX yet. Hope this would be helpful.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Re: Rankx Not working as expected when data is filtered using slicers and filter pane

Vivek26 — Mon, 06 Jan 2025 18:48:33 GMT

Anonymous . Thanks a lot for the reply. The solution worked.

Please do let me know if you are able to solve it with Rankx as well.

I also Invested a lot of time with Rankx but I was not able to find a good solution.