Filter a table on a a rank

[Michiyo]

Hi !
I have calculated ranked using this formula which worked perfectly well : 
DAX :

RANK = RANKX(ALL(td_store[LIB_MAG]), CALCULATE(SUM(tf_market_share_by_store_nomcenclature[marche]),ALLEXCEPT(td_store,td_store[LIB_MAG])))

I have a slicer on the LIB_MAG which filter correctly my table : 

However I don't want to see only the selected mag but the five previous and the last following mag, basing on the rank (in this exemple, the rank 121 to 131) like this : 

Note that the rank is filtered on the LIB_RAY selected on an other slicer, so I did not succeed to pass by temporary tables ...

Have you got ideas how to do this ?

Thank's for your help !

[v-jayw-msft · Accepted solution]

Hi @Michiyo ,

Please refer this measure:

measure = 
var selected_rank = MAXX(FILTER(ALL(td_store),td_store[LIB_MAG]=SELECTEDVALUE(slicer[LIB_MAG])),[Rank])
return
IF(td_store[Rank]>=selected_rank-5&&td_store[Rank]<=selected_rank+5,1,0)

Add this measure to visual filter and set value = 1.

Best Regards,

Jay

[rohit_singh]

Hello @Michiyo ,

Please try the following : 

1) Create a table with a single column that only contains the field you will be filtering on i.e. LIB_MAG. This is called a disconnected table and will not have any relationships with other tables in your model. This table will be used to filter your main table.

2) Create a measure on your main table that will act as the filter. 

_sel is the value of LIB_MAG that we obtain from the disconnected table.
_range is the number of rows on either side you want to display. I've taken 2 rows above and below
_min is the rank of the first row that will be selected
_max is the rank of the last row that will be selected 

3) On the report view, add the columns from the main table on a table visual and set the measure created in the previous step as a filter on the visual = 1. Add the LIB_MAG column from the disconnected table as the slicer. 
You will now be able to see the selected LIB_MAG value along with previous and next two rows in the table.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Michiyo]

Hi rohit_singh
Thank's a lot for your answer.
I tried your solution and I understand how it can works, however in my case, the RANK il a measure, not a column, so I've got this error : 

It's all the difficulty of the problem ... The RANK il calculated with other selected values (slicer on category), and I don't success to pass the problem ...
I tried that but I don't success to extract the rank of the selected mag :

If you have another idea ... Thank you again for helping me !