Forum Discussion
Helper IVadd 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 will not work.
Index is for data Import.
I am using Direct Query.
12 Replies
- wdx223_Daniel
Community Champion
=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
space83Hi 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.
- 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:
- 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])
- 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 ) ) )
