Forum Discussion

mbailey's avatar
mbailey
Icon for Helper II rankHelper II
6 years ago

Measure to select 2nd row in sorted table?

Hi, I have a table that I want to sort by a date field, DESC, and then always use the 2nd row in that table in a Measure calculation. The purpose of this measure will be to return the percent_on_tim...
Need Help
mbailey's avatar
mbailey
Icon for Helper II rankHelper II
6 years ago

Hi,

Thank for your help, but neither of these work. The first doesn't return anything = 0, and the second doesn't work because of the EARLIER function for some reason. I ran into this before trying that. I think it has to do with a direct Query or something. Also, if you look at your results, you are adding a new column and the result is 100 each row. I'm just trying to create one Measure that will grab the Percent On-Time value for the second row in the table - which will always be the "Previous Day" regardless of dates since a record will not even exist for a "previous day" if nothing happened on a particular day.

Mindful that my data looks like this below, so in this example, I'm trying to create a measure to pick up "100.00" from row 2 that I can put on a Card visualization for "Previous Day Average".  And again, I don't care about the due_date, I just always need to get the 2nd row.

 

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, mbailey 

I’m sorry I didn’t notice that your connection mode is directquery, and  calculation column formula in my previous sample can not be applied  as measure.

 

However, when I re-test my first formula  as measure in DirectQuery mode, it  can show nicely the second row of data .

Please test whether the following formula will  return the date of  the second row，

If possible, please share the screenshot of the result on the desktop for further research.

 

 

measure due_date = 
MINX( 
    TOPN (
        2,
        DMTA_OnTimeShipmentSummary,
        DMTA_OnTimeShipmentSummary[due_date], DESC
    ),
    DMTA_OnTimeShipmentSummary[due_date]
)

 

 

 

BTY,you may  consider creating a index column in database,It will better help you solve this  problem.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.