Forum Discussion

6 Replies

  • RossEdwardsRio's avatar
    RossEdwardsRio
    Frequent Visitor
    3 days ago

    This can be done with a DAX measure but you will need 2 extra things in your data:

    1. A date column that indicates when the entry was made
    2. 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:



  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    4 days ago

    Hi 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 & " -> " & _Chain

    Please see the attached pbix. 

    Note: solution was done with the help of AI.

    • damit230183's avatar
      damit230183
      Icon for Helper II rankHelper II
      4 days ago

      Hi,

      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

      • ShivekMaharaj's avatar
        ShivekMaharaj
        Icon for Memorable Member rankMemorable Member
        4 days ago

        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.