ZigZag pattern as new Column for ID

Hi damit230183​,

Since you're using DirectQuery with roughly 5 million rows per day, I would change the approach rather than trying to reproduce the Power Query index logic in DAX.

The pattern you want is not really a row-level calculation. To turn:

3 -> 8
8 -> 9

into:

3 -> 8 -> 9

you need to compare/traverse multiple rows for the same tagid.

Microsoft's current DirectQuery guidance notes that calculated columns on relational DirectQuery tables are limited to row-level expressions that can be translated to the source. That makes this type of recursive/iterative chain-building a poor fit for a DirectQuery calculated column.

For this volume, I would push the transformation upstream instead.

Ideally, create a source-side table such as:

tagid      Pattern
1            3 -> 8 -> 9
2            4 -> 7 -> 10 -> 12

and update that table incrementally as new operations arrive.

If you cannot materialize another table, the next option would be a database view that derives the chain at the source. For example, on SQL Server/Azure SQL this can be implemented with recursive SQL logic that follows:

PrevOp -> CurrentOp -> next CurrentOp

for each tagid.

Microsoft's query folding guidance specifically recommends moving transformations into the source for DirectQuery when they cannot be folded efficiently.

I would avoid generating an index in Power Query over 5M daily rows, because DirectQuery depends heavily on keeping the transformation foldable.

If you tell me what database is behind the DirectQuery connection (SQL Server, Snowflake, Oracle, etc.), I can suggest the exact source-side pattern for building the chain.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.