Forum Discussion

damit230183's avatar
damit230183
Icon for Helper II rankHelper II
1 month ago
Solved

ZigZag pattern as new Column for ID

Hi,

I am looking for ZigZag pattern for the report,

I have this 

I would like to show table like this


Thanks

 

Need Help
power bi
Tips and Tricks
  • RossEdwardsRio's avatar
    RossEdwardsRio
    1 month 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:



7 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 month 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
      1 month 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

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 month ago

        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. 

  • RossEdwardsRio's avatar
    RossEdwardsRio
    Icon for Resolver I rankResolver I
    1 month 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: