add running no column via direct query

[space83]

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 will not work.

Index is for data Import. 

I am using Direct Query.

[space83]

Hi @wdx223_Daniel ,

Now it works. I am using below formula. Thanks for your help.

No = VAR _order =
    MAX ( OrderIntake[Order No] )
VAR _date =
    MAX ( OrderIntake[Status Date] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( OrderIntake ), OrderIntake[Order No], OrderIntake[Status Date] ),
            ISONORAFTER ( OrderIntake[Order No], _order, , OrderIntake[Status Date], _date, ASC )
        )
    )

[space83]

Hi @wdx223_Daniel,

Thanks for your help but I think that is not the solution.

Once I do filtering, the running no will messy as u can see below:

[wdx223_Daniel]

@space83 dose it work to change ALL to ALLSELECTED?

[space83]

Hi @wdx223_Daniel ,

After I look in deep, my data is not correct. For duplicate Order No and Status Date it will show once. Thus create a running no will not solve the problem.

I just use the syntax below and I got result i want which is total of order for 1 interval date.

Total Order No = COUNT(OrderIntake[Order No])

[wdx223_Daniel]

@space83 DAX can not know which one is first if all the columns is identical.

[space83]

Hi @wdx223_Daniel,

I received output as below which is got duplicate running no. I think the DAX syntax need to edit some where.

[wdx223_Daniel]

@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]

Hi @wdx223_Daniel ,

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

[wdx223_Daniel]

@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,  )
        )
    )

[wdx223_Daniel]

=VAR _order=MAX(Table[Order No]) VAR _date=MAX(Table[Status Date]) RETURN COUNTROWS(FILTER(SUMMARISE(ALL(Table),Table[Order No],Table[Status Date]),Table[Order No]<_order&&Table[Status Date]<_date))+1

[space83]

Hi @wdx223_Daniel,

What is this? where should I put this snippets? New column, New measure?

[wdx223_Daniel]

@space83  it's a measure, create a new measure with that code, then drag it into the visual.