Table Visual Filtering

[ChrisPBIUser]

I've had a look round and cannot find a potential solution to my issue.  

I'm looking to filter a table visual to just 5 rows based on ranking of sales and a Agent Slicer.  So my table has 3 columns Agent, Sales Date and Sales and a Rank measure based on sum sales.  One table visual is easy enough to do is Top 3 but I need another table to show by rank just 5 other agents based on a slicer Agent seletion. 

So if the Agent that is selected is ranked 50th, in the table I need to see the two agents ranked above and below so ranks 48th, 49th, 50th, 51st and 52nd.  So basically you can see a selected groups performance against the top 3 table visual.  

[v-henryk-mstf]

Hi @ChrisPBIUser ,

Hello, I cannot open the link you shared. You need to upload it to OneDrive and then share link. This method is preferable. Looking forward to your reply as soon as possible, I will solve your problem as soon as possible.😀

Best Regards,
Henry

[ChrisPBIUser]

@v-henryk-mstf thanks for assisting, I've just tried the link above which is one drive and the Excel file opens for me but that's probably because it's my One Drive - https://onedrive.live.com/edit.aspx?resid=E9A224AF3C65C846!10013&ithint=file%2cxlsx&authkey=!ACDftt4...

The file is simple, three columns.  Order Date, Agent and Sales and about 9000k rows.  So different daily dates from 2017 through to 2020, about 300 names for agents and a sales value amount.  This is just random data I pulled from the net as I'll be able to apply any answer to the actual data I have

[v-henryk-mstf]

Hi @ChrisPBIUser ,

The link you shared is password protected, you need to follow the sharing steps below:

Best Regards,
Henry

[ChrisPBIUser]

@v-henryk-mstf , knew it would be me, try this link https://1drv.ms/x/s!AkbIZTyvJKLpzh0abswIDDLsYHkf?e=uyvDuO

[ChrisPBIUser]

@v-henryk-mstf  has the link worked this time?

[ChrisPBIUser]

@amitchandak morning, I've been playing around with this but I still get errors on the original answer or I just get the single selected value.  Just wondering if you have had chance to look into this

[ChrisPBIUser]

@amitchandak  thanks again for your quick response, I can see what it's trying to do but I get an error which I can't resolve image below plus image of the two tables I now have. 

[amitchandak]

@ChrisPBIUser , In case only need rank based on Agent, then  you should not have date in the tbale

Rankx(allselected(Table[Agent]), [Sales]) //Sales is a measure

Rankx(allselected(Table[Agent], Table[Date]), [Sales]) //Sales is a measure, with agent and date

You can now use visual level filter

You can also use TOPN

https://www.youtube.com/watch?v=QIVEFp-QiOk

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
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[ChrisPBIUser]

@amitchandak  thanks for the quick response.  

The measure I have in place is 

Rank = RANKX( ALLSELECTED( Orders[Agent]) , CALCULATE( SUM( Orders[Sales] ) ) )

The order date isn't actually used in the table visual but just actually in the query when loaded.

What I need is select an Agent from a slicer and for the table visual to display that agent plus the two above and below based on the relevant rank for the agent selected.

Example, the table circled black is fixed using Top N > Top 3, thats fine.  The table in red needs to display just 5 Agents based on the middle agent (blue arrow).  So if I select Agent Zara Davidson the table filters to the 2 above her and two below her so as she is ranked 107th, the table needs to display ranks 105 through to 109.  (names and sales values are fictitious)

[amitchandak]

@ChrisPBIUser , for that you need an independent Agent table and then try a measure like

measure =
var _rank= maxx(filter(values(Orders[Agent]),[Agent] in selectedValue(Agent[Agent])),[Rank])
return
calculate([Rank], filter(values(Orders[Agent]), [Rank] >= _rank -2 && [Rank] >= _rank +2))

Refer independent table -https://www.youtube.com/watch?v=lOEW-YUrAbE

[ChrisPBIUser]

@amitchandak I have sample data but can't seem to upload it

[amitchandak]

@ChrisPBIUser , you can upload to dropbox or onedrive and share a link 

[ChrisPBIUser]

@amitchandak ah yes forgot - https://1drv.ms/x/s!AkbIZTyvJKLpzh0g37beMD5uFKUg?e=4qg3G4