Forum Discussion
How can we show Top 10 Records In Table Visual
Hi All,
I have a table that contains the job name , avg.execution time as columns.
I want to show in the table visual that top 10 long running jobs based on avg.execution time. Using DAX can you provide details how can we achive the same. I don't want to apply any visual filter using Top N filter in Advanced filter option. All I need to get the result by writing only the DAX single measure.
Thanks,
Sri
My suggestion is to create a calculated table
TopJobs=TOPN ( 10, TableJob, TableJob[AvgExc], DESC )and then in the table visual take data from the above tableIf this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
3 Replies
My suggestion is to create a calculated table
TopJobs=TOPN ( 10, TableJob, TableJob[AvgExc], DESC )and then in the table visual take data from the above tableIf this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi FBergamaschi, Thanks for your solution.
Hi,
you can write a measure, that will use RANK or TOPN function, and then use it as a filter to the visual or just linit the visibile elements with IF statement.
Approach with RANK and limiting the displaying elements with IF:Sample measure = VAR _table = ADDCOLUMNS( ALLSELECTED( Your_table[Job name] ) , "value", [avg.execution time] ) VAR _rank = RANK( DENSE , _table , ORDERBY( [value] , DESC BLANKS LAST ) ) VAR _exec_time = [avg.execution time] VAR Result = IF ( _rank <= 10 , _exec_time ) RETURN ResultIf you want use measure as a filter you just need to change the last part of code to:
VAR Result = IF ( _rank <= 10 , 1 ) , you also delete VAR _exec_time in this scenario.
