Forum Discussion
Filter by Field
Hi, May I please ask if its possible to filter Top 5 by field. The field contains Dom Air, Reg Air , Int Air. I would like to filter top 5 routes by Trns used by Dom Air, Reg Air, Int Air in one visual. Not sure if possible
5 Replies
Nas ,
In the visual level filter, you can use TOP N on the group by and Measure
You can create a Top N measure and that will filter if there is no other measure or use this a filter
example
Top 5 City Rank = CALCULATE([Sales],TOPN(5,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
You can create rank and filter
City Rank = RANKX(all(Geography[City]),[Sales])
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
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Hi Nas ,
According to my understanding, you want to select top5 data based on three columns, right?
You could use the following formula:
rankColumn = VAR a = [Dom Air] VAR b = [Reg Air] VAR t1 = FILTER ( ALL ( 'TopNTable' ), TopNTable[Dom Air] = a ) VAR t2 = FILTER ( ALL ( 'TopNTable' ), TopNTable[Dom Air] = a && 'TopNTable'[Reg Air] = b ) RETURN RANKX ( t1, RANKX ( t1, [Reg Air],, DESC, SKIP ) * 100 + RANKX ( t2, [Int Air],, DESC, SKIP ) * 10, , ASC, SKIP )Then apply visual's filter : is less than or equal to 5 to visual :
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @Eyelyn9
Thank you so much for your response.
Yes, top 5 data but based on one column. The column contains Dom Air, Reg Air, Int Air.
One Column
Dom Air Route Details 12
Route Details 11
Route Details 10
Route Details 9
Route Details 8
Int Air Route Details 12
Route Details 11
Route Details 10
Route Details 9
Route Details 8
Reg Air Route Details 12
Route Details 11
Route Details 10
Route Details 9
Route Details 8
Hope I am making sense
In
Hi Nas ,
It seems like the the are in the same column and you want to calculate each category's Top5 value, right?
rankTop5 = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[Column1] = MAX ( 'Table'[Column1] ) ), CALCULATE ( MAX ( ( 'Table'[Value] ) ) ), , DESC )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi Nas ,
Sorry to disturb you...
But did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn Qin
