Counting transactions given they precede a specific transaction value

[ZachWilkins]

I have data in the format below where each transaction per ID is captured. I am trying to get a count of the transactions an ID was in prior to being rejected.

So for XYZ it would be:

Acknowledge Receipt - 1

Pending Approval - 1

And overall the counts would look like:

Acknowledge Receipt - 1

Pending Approval - 3

Approve Request - 1

I've tried adding a rank to offset the data but it's made complicated by ID's that have been rejected multiple times. Then adding on to that, I also wanted to see how long something sat in a rejection status prior to moving to the next step, or if it's still in rejection status. But I wanted to focus on one problem first. I'm a little stumped by how to solve this.

Data

[ZachWilkins]

Ok so what you provided is very close but it counts too many steps per transaction. For example, if we look at ID XYZ, that ID was rejected only once. Therefore, in the table you created it should only have a 1 in the Pending Approval column. I am only interested in the most previous transaction prior to rejection. Acknowledge Receipt does occur prior to Reject Request but it is 3 transactions prior. DEF was rejected twice, so there should be 2 in Pending Approval only.

[v-jianpeng-msft]

Hi, @ZachWilkins 
Thank you very much for your reply. Based on your description, I pictured the output you described in the previous reply. Where are your doubts? In general, each ID is grouped first, and then a calculated column is added to determine whether the current row is rejected. Finally, the Pending Approval before rejection is counted. Only transactions before rejection are counted.

Best Regards

Jianpeng Li

[v-jianpeng-msft]

Hi, @ZachWilkins 

Based on your description, I've used the following sample data:

In my example data: for XYZ, it would be:
Acknowledge receipt - 1
Pending approval - 1

for DEF, it would be:
Acknowledge receipt - 1
Pending approval - 2
As you said, first create a rank column, using the following DAX:

Transaction Rank = RANKX(
  FILTER(
    ALL('Table'),
    'Table'[ID] = EARLIER('Table'[ID])
  ),
  'Table'[TRANSACTION_DATE],
  ,
  ASC,
  Dense
)

You also need to create a column that determines whether the current row is rejected or not:

Is Rejected = IF('Table'[TRANSACTION_TYPE] = "Reject Request", 1, 0)

The results of the two columns are as follows:

The following measures are then created:

Acknowledge Receipt Before Rejection = 
VAR _ID  =SELECTEDVALUE('Table'[ID])
RETURN 
CALCULATE(
  COUNTROWS('Table'),
  FILTER(
    ALL('Table'),
    'Table'[ID] = _ID &&
    'Table'[Transaction Rank] < MINX(
      FILTER('Table', 'Table'[Is Rejected] = 1),
      'Table'[Transaction Rank]
    )&&
    'Table'[TRANSACTION_TYPE]="Acknowledge Receipt"
  )
)

You'll need to modify the contents of the quotation marks in 'Table'[TRANSACTION_TYPE]='Acknowledge Receipt' and create two more measures(Pending Approval Before Rejection, Approve Request Before Rejection) as follows:

Next, you'll need to create the corresponding metrics using the following DAX template:

Acknowledge Reciept = 
VAR _table = SUMMARIZE('Table','Table'[ID],'Table'[TRANSACTION_TYPE],'Table'[TRANSACTION_DATE],"Reciept",[Acknowledge Receipt Before Rejection],"Pending",[Pending Approval Before Rejection],"Request",[Approve Request Before Rejection])
VAR _table2 = SUMMARIZE(_table,'Table'[ID],"maxReciept",MAXX(FILTER(_table,'Table'[ID]=EARLIER('Table'[ID])),[Reciept]),"maxPending",MAXX(FILTER(_table,'Table'[ID]=EARLIER('Table'[ID])),[Pending]),"maxRequest",MAXX(FILTER(_table,'Table'[ID]=EARLIER('Table'[ID])),[Request]))
RETURN MAXX(FILTER(_table2,'Table'[ID]=SELECTEDVALUE('Table'[ID])),[maxReciept])

For the other two metrics, you need to modify the [maxReciept] in RETURN MAXX(FILTER(_table2,'Table'[ID]=SELECTEDVALUE('Table'[ID])),[maxReciept]).

The final result is as follows:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.