Count how many times customers change their orders

[Anonymous]

Hi, Im still a beginner in Power BI and Im stuck with one measure.

For example i have one table with 10 ID and when a customer pay for the order it get status "Confirmed". But before we deliver the order the customer can change the order untill 24H before delivery date.
I want to know how many times our customers change their orders and on which Hour they do.

So for example the data can look like this
Columns are Order ID, Order status, timestamp and Hour where Hour i extracted Hour from timestamp.

Order ID,         Order status,               timestamp               Hour
1                     Confirmed                2018-01-11 06:14       06

1                     Confirmed                2018-01-12 09:14       09

1                    Confirmed                2018-01-13 01:14       01

2                    Confirmed                2018-01-14 18:14       18

2                    Confirmed                2018-01-15 07:14       07

2                    Confirmed                2018-01-16 12:14       12

2                    Confirmed                2018-01-17 19:14       19

3                    Confirmed                2018-01-18 07:14       07

3                    Confirmed                2018-01-19 03:14       03

3                    Confirmed                2018-01-20 02:14       02

3                    Confirmed                2018-01-23 23:14       23

3                    Confirmed                2018-01-24 11:14       11

4                    Confirmed                2018-01-25 07:14       07

4                    Confirmed                2018-01-26 08:14       08

4                    Confirmed                2018-01-27 07:14       07

4                    Confirmed                2018-01-28 17:14       17

4                    Confirmed                2018-01-29 20:14       20

4                    Confirmed                2018-01-30 07:14       07

4                    Confirmed                2018-01-31 16:14       16

If i take stacked column chart and have Hour as Axis and drag Order ID to Value and use Count i will get that for example on hour 07 i will get 5 that means our 4 customers for that month have changed in total 5 times on between 07:00 and 07:59. But the problem here is that for Order ID = 5 and 4, their first Confirmed status are on Hour 07, And first Confirmed status for each order is when the customer have paid first time and dont have made any changes. So in total i want to have 3 changes instead of 5 on my stacked column when we check on Hour 07.

How can i easy create an measure that count all the confirmed but not include the first confirmed status for each Order ID? ofcourse if one Order ID have only one Order status that means the customer havent done any changes yet and i dont want to see that.

Hope you all understand what im after and thanks for all the answer i can get

[Mercator_1980 · Accepted solution]

Hi,

my Suggestion would be to:

1. Calculate a Ranking per Order-ID

2. Calculate or create graphics while filtering out everything, which is Rank 1

To calculate a Ranking per Order-ID try this DAX-Formula to create a new column:

RANKX(FILTER('Table';'Table'[Order-ID]=EARLIER('Table'[Order-ID]));'Table'[timestamp];;ASC;Dense)

I got the idea from here: http://community.powerbi.com/t5/Desktop/rankx-by-group/m-p/52722#M21220

This should create a colum which contains a Rank, for each ORDER-ID starting at the lowest time with 1 up to the highest 1+n.

Now you can filter your tables in a second step or create a summarized table which does not contain Ranks of number one.

Does this help?

[Anonymous]

Hi, it worked perfectly. Very simple and easy steps to follow.
Thanks for help