Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Top 3 highest times

Hi everyone, 

I want to show the top 3 highest times for each day. The measure is as follows:
Top3 = 

IF(
   NOT ( ISBLANK([AvgTimeInSec]) ),
    RANKX(
        FILTER(
            ALL(RE_IQM[Index]),
             
                               
             NOT( ISBLANK( [AvgTimeInSec]) )) , [AvgTimeInSec], , DESC, Dense
             )
        )
 
I have selected 'is less than or equal to 3' in the Filter pane. 
However, for this to work, I have to add the column Index. That information is not crucial in this case; I just want to have the top 3 highest times for each day. 
Is there a way to only have the column Date and the measure which return directly the top 3 in a table?
 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Ahmedx ,

    Thanks for your input. Unfortunately, it wasn't the answer I was looking for. 
    I created a new table first, with the columns Date, Index and Time. I added a RankX column too, based on the highest times per day. 

    Finally, I created a measure using Concatenatex: 

    ConcatenateHighestTime =
    CONCATENATEX(
        TOPN(3,
        TopnTable,
        [AvgTimeInSec], DESC), [AvgTime], UNICHAR(10), [AvgTimeInSec], DESC)

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago
    Top3 = 
IF(
   NOT ( ISBLANK([AvgTimeInSec]) ),
    СALCULATE([AvgTimeInSec],TOPN(3,
        FILTER(
            ALL(RE_IQM[Index]),
             
                               
             NOT( ISBLANK( [AvgTimeInSec]) )) , [AvgTimeInSec], , DESC, Dense
             )
        ))
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ahmedx ,

      Thanks for your input. Unfortunately, it wasn't the answer I was looking for. 
      I created a new table first, with the columns Date, Index and Time. I added a RankX column too, based on the highest times per day. 

      Finally, I created a measure using Concatenatex: 

      ConcatenateHighestTime =
      CONCATENATEX(
          TOPN(3,
          TopnTable,
          [AvgTimeInSec], DESC), [AvgTime], UNICHAR(10), [AvgTimeInSec], DESC)