Forum Discussion

space83's avatar
space83
Icon for Helper IV rankHelper IV
5 years ago

add running no column via direct query

Below is example of my report.   How can I create new virtual column and display running no of the record?   it will auto count.   Please dont tell me using 'add column > index' since it wi...
Need Help
Show and Tell
Tips and Tricks
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago

space83 try this

=
VAR _order =
    MAX ( Table[Order No] )
VAR _date =
    MAX ( Table[Status Date] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE ( ALL ( Table ), Table[Order No], Table[Status Date] ),
            ISONORAFTER ( Table[Order No], _order, DESC, Table[Status Date], _date, DESC )
        )
    )
space83's avatar
space83
Icon for Helper IV rankHelper IV
5 years ago

Hi wdx223_Daniel ,

I do not understand, somehow the count is not start with 1. U can refer pic as below:

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    space83 this depends on what order your date is, in your snapshot, order no and date is sorted descending. so you need change code to 

    =
VAR _order =
    MAX ( Table[Order No] )
VAR _date =
    MAX ( Table[Status Date] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE ( ALL ( Table ), Table[Order No], Table[Status Date] ),
            ISONORAFTER ( Table[Order No], _order, , Table[Status Date], _date,  )
        )
    )