Create Column Containing Transaction Pattern

Power BI Data Model
1

Hi Experts,

I have a data with [Material], [Batch],[Movement Type], [Document Date] and [Time of Entry]. I want to create a column that would show the pattern of movement type according to date.

Raw Data:

image

For example, Material / Batch – > 9121313 / 00471124 has movement pattern of 101 - 601. The pattern should follow the document date and time stamp. If the earliest transaction is 101, followed by 102 then finally 601, the pattern should be 101 - 102 - 601.

So I want to create a table like this:

image

How can I do this? This will help me explore the data regarding unusual patterns.

Thanks,
Joey Henry

2

Hi @joeyhenry

Could you please help to share the sample file.

3

Hi @joeyhenry,

It’s always great if you include sample file, however I think you are looking for a pattern like below:

image
image1050×717 110 KB

DAX:

calculated column =
CALCULATE (
    CONCATENATEX (
        SUMMARIZE ( 'Table1_2', [Batch], [Material], [Movement Type] ),
        [Movement Type],
        " - "
    ),
    ALL ( Table1_2[Movement Type] )
)
4

Hi @hafizsultan,

This is almost what is needed for the analysis. The only one missing is that the sequence must follow a pattern according to the date and time stamp. For example, if the earliest transaction is 101 followed by 102 and last transaction is ZS5, then the pattern should be 101 - 102 - ZS5.

This is really helpful when analysing anomalous/unusal transactions. From a visualisation standpoint, I want to show the connections of the movement. Like a chain. I got some promising visualisation tools such as “journey map” or “force directed graph” but kind of lost for now.

Not sure how to attach file in the forum yet. I am so new here.

Thanks,
Joey Henry

5

Hi @joeyhenry,

It makes sesne. Please use below icon to upload you file.

image
image936×169 9.43 KB

6

YGTTMH002 Apr 2017.xlsx (3.0 MB)

Here’s an example of the data.

7

Hi @joeyhenry,

You can give attached file a try. I extracted columns from your file, merge data & time column to create datetime and use below formula. You may need to test the dax and change a bit to suit your requirements.

Calculated Column =
CALCULATE (
    CONCATENATEX (
        SUMMARIZE ( 'Table', [Batch], [Material], [Movement Type] ),
        [Movement Type],
        " - ",
        CALCULATE ( MAX ( 'Table'[Merged] ), ALL ( 'Table'[Material], 'Table'[Batch] ) ), ASC
    ),
    ALL (
        'Table'[Material Doc. No.],
        'Table'[Material Type],
        'Table'[Movement Type],
        'Table'[Material Doc. No.],
        'Table'[Merged]
    )
)

EDNA_ConcatenateX.pbix (516.0 KB)

8

Hi @joeyhenry, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

9

Hi EnterpreiseDNA,

I followed the logic that @hafizsultan gave and programmed it in power query. Not as clean but it worked.

image
image957×593 43.2 KB

And now here is the result:

image
image646×610 27.4 KB

Thanks @hafizsultan and @EnterpriseDNA. I tried the DAX formula but since I have 6 mio rows, pBI told me I don’t have enough memory to run it. So, I programmed it in power query through another table using the logic a similar logic in the DAX formula that @hafizsultan gave.

Sincerely,
Joey Henry

1 Like
10

Hi,
Can you share the pbix with the implemented logic of your so i can look into it and learn few things.

Thanks,
Anurag