Forum Discussion
Helper IIZigZag pattern as new Column for ID
This can be done with a DAX measure but you will need 2 extra things in your data:
- A date column that indicates when the entry was made
- A record for the first version of the "Op".
Here is the datatable i made which i just called "Data"
Here is the measure:
Pattern = CONCATENATEX(Data, Data[CurrentOp], " -> ", Data[Date], ASC )Here is the output in a table visual:
Super UserHi damit230183
Assuming the rows are ordered chronologically, create an index column in Power Query to identify which row comes first if there isn't any column in your table that indicates such
Then try this:
Pattern = VAR _CurrentID = 'Table'[id] -- Isolate all rows for the current ID VAR _RelatedRows = FILTER ( ALL ( 'Table' ), 'Table'[id] = _CurrentID ) -- Find the true starting row (highest Index = bottom row = Index 1) VAR _MaxIndex = MAXX ( _RelatedRows, 'Table'[Index] ) -- Extract the starting PrevOp (3 from Index 1) VAR _StartNode = LOOKUPVALUE ( 'Table'[PrevOp], 'Table'[id], _CurrentID, 'Table'[Index], _MaxIndex ) -- Concatenate CurrentOp from bottom to top (Index 1 then Index 0) VAR _Chain = CONCATENATEX ( _RelatedRows, 'Table'[CurrentOp], " -> ", 'Table'[Index], DESC ) RETURN _StartNode & " -> " & _ChainPlease see the attached pbix.
Note: solution was done with the help of AI.
damit230183Hi,
Thanks for your response.
So i am using direct query due to very high db volume (atleast 5 Mil everyday). So some of the syntax like Index, ConcatenateX etc I can not use.
Thanks
- danextian
Unfortunately, your requirement requires iteration which isn't supported in Direct Query. If you have that big of a data, I wouldn't do a full load each refresh but use incremental refresh instead.
- ShivekMaharaj
Community Champion
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 -> 9into:
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 -> 12and 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.
