Forum Discussion

Koritala's avatar
Koritala
Post Patron
1 year ago
Solved

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 pr...
Need Help
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    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 table
     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

lmolus's avatar
lmolus
Advocate II
1 year ago

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
Result

 If 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.