locked
How to filter a DAX query when making an Excel table using TOPN RRS feed

  • Question

  • Hi

    I'm using a DAX query in Excel 2013 to load an Excel table with data from a PowerPivot model so I can use OFFSET and a scrollbar in a dashboard, but I need help with the filtering. The following code works:

    EVALUATE
       FILTER(
          SUMMARIZE(
             History,
             History[OrderCloseTime],
             History[OrderSymbol],
             History[OrderType],
             History[OrderLots],
             History[OrderProfit],
             History[OrderPips],
             History[OrderClosePrice],
             History[OrderTicketNumber],
             History[OrderOpenTime],
             History[OrderOpenPrice],
             History[OrderStopLoss],
             History[OrderTakeProfit],
             History[OrderCommission],
             History[OrderSwap],
             History[OrderComment]
          ),
          History[OrderCloseTime]>=TODAY()-90
       )
    ORDER BY [OrderCloseTime] DESC
    but what I'd really want is to change the filter from "everything in the last 90 days", to fetch only the last 100 rows ordered by History[OrderCloseTime] descending, but I can't figure out how to use TOPN with a sorted OrderCloseTime. The DAX editor doesn't give me enough feedback to figure it out on my own.

    TIA!

    Re

    Dennis


    Friday, December 6, 2013 1:01 PM

Answers

  • Hi Dennis,

    I have taken a subset of your table columns and inserted 150 rows of test data. Each rows value is based on a row number between 1 and 150 to help keep track of the behaviour in this example since listing all 150 rows will cause excessive scrolling:

    OrderCloseTime          OrderSymbol          OrderType          OrderComment
    08/03/2013 Symbol 1 Order Type 1 Comment 1
    09/03/2013 Symbol 2 Order Type 2 Comment 2
    10/03/2013 Symbol 3 Order Type 3 Comment 3
    11/03/2013 Symbol 4 Order Type 4 Comment 4
    12/03/2013 Symbol 5 Order Type 5 Comment 5
    13/03/2013 Symbol 6 Order Type 6 Comment 6
    14/03/2013 Symbol 7 Order Type 7 Comment 7
    15/03/2013 Symbol 8 Order Type 8 Comment 8
    16/03/2013 Symbol 9 Order Type 9 Comment 9

    ...up to...

    31/07/2013 Symbol 146 Order Type 146 Comment 146
    01/08/2013 Symbol 147 Order Type 147 Comment 147
    02/08/2013 Symbol 148 Order Type 148 Comment 148
    03/08/2013 Symbol 149 Order Type 149 Comment 149
    04/08/2013 Symbol 150 Order Type 150 Comment 150

    Since the TOPN function signature is...

    TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

    I have defined the following DAX query that gets the last 100 rows based on the [OrderCloseTime] column and then orders the result set by [OrderCloseTime] in descending order:

    EVALUATE
    TOPN(
      100,
      SUMMARIZE(
        'History',
        'History'[OrderCloseTime],
        'History'[OrderSymbol],
        'History'[OrderType],
        'History'[OrderComment]
      ),
      'History'[OrderCloseTime],
      0
    )
    ORDER BY 'History'[OrderCloseTime] DESC

    Note: For the last parameter of the TOPN function I have used '0' which tells the TOPN function to internally arrange the values in descending order before getting the TOP 100 rows.

    Here's the output for the very top of the returned results...

    ...and after scrolling to the very bottom...

    undefined

    Hope this helps.

    Michael



    • Proposed as answer by Michael Amadi Sunday, December 8, 2013 10:02 AM
    • Edited by Michael Amadi Sunday, December 8, 2013 10:09 AM minor edit
    • Marked as answer by sgude1 Sunday, December 8, 2013 7:46 PM
    Sunday, December 8, 2013 10:02 AM

All replies

  • Hi Dennis,

    I have taken a subset of your table columns and inserted 150 rows of test data. Each rows value is based on a row number between 1 and 150 to help keep track of the behaviour in this example since listing all 150 rows will cause excessive scrolling:

    OrderCloseTime          OrderSymbol          OrderType          OrderComment
    08/03/2013 Symbol 1 Order Type 1 Comment 1
    09/03/2013 Symbol 2 Order Type 2 Comment 2
    10/03/2013 Symbol 3 Order Type 3 Comment 3
    11/03/2013 Symbol 4 Order Type 4 Comment 4
    12/03/2013 Symbol 5 Order Type 5 Comment 5
    13/03/2013 Symbol 6 Order Type 6 Comment 6
    14/03/2013 Symbol 7 Order Type 7 Comment 7
    15/03/2013 Symbol 8 Order Type 8 Comment 8
    16/03/2013 Symbol 9 Order Type 9 Comment 9

    ...up to...

    31/07/2013 Symbol 146 Order Type 146 Comment 146
    01/08/2013 Symbol 147 Order Type 147 Comment 147
    02/08/2013 Symbol 148 Order Type 148 Comment 148
    03/08/2013 Symbol 149 Order Type 149 Comment 149
    04/08/2013 Symbol 150 Order Type 150 Comment 150

    Since the TOPN function signature is...

    TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

    I have defined the following DAX query that gets the last 100 rows based on the [OrderCloseTime] column and then orders the result set by [OrderCloseTime] in descending order:

    EVALUATE
    TOPN(
      100,
      SUMMARIZE(
        'History',
        'History'[OrderCloseTime],
        'History'[OrderSymbol],
        'History'[OrderType],
        'History'[OrderComment]
      ),
      'History'[OrderCloseTime],
      0
    )
    ORDER BY 'History'[OrderCloseTime] DESC

    Note: For the last parameter of the TOPN function I have used '0' which tells the TOPN function to internally arrange the values in descending order before getting the TOP 100 rows.

    Here's the output for the very top of the returned results...

    ...and after scrolling to the very bottom...

    undefined

    Hope this helps.

    Michael



    • Proposed as answer by Michael Amadi Sunday, December 8, 2013 10:02 AM
    • Edited by Michael Amadi Sunday, December 8, 2013 10:09 AM minor edit
    • Marked as answer by sgude1 Sunday, December 8, 2013 7:46 PM
    Sunday, December 8, 2013 10:02 AM
  • Wow! All I was missing was a zero at the end. So close, yet so far away. Thanks a lot. This works great.

    Re

    D

    Sunday, December 8, 2013 7:46 PM