How to count the rows where sequence of the process is incorrect?

[Anonymous]

Hi all,

I am fairly new to PBI and I need help figuring out solution to a problem I am facing.

I have given below a simplified version of the data set I am handling.

I need to count the number of rows which have the steps in the wrong order by date. I also need to display the information of candidate whose order is wrong.

The output would look something like :

Egs: Here I would need to display the details of candidate ID: 104 as the order of steps is wrong for the candidate.

For candidate ID 104, the date of step "3. Offer" comes before the Screening date, which is in the wrong order.
I tried creating a calculated column below, but it doesn't seem to do the trick.

# of wrong sequence = COUNTAX(FILTER('Status Changes', EARLIER('Status Changes'[Job  ID]) = 'Status Changes'[Job ID] && EARLIER('Status Changes'[Candidate ID]) = 'Status Changes'[Candidate ID] && EARLIER('Status Changes'[Application ID]) = 'Status Changes'[Application ID]), IF ( 'Status Changes'[Created Date]> EARLIER ('Status Changes'[Created Date]) && 'Status Changes'[Steps]< EARLIER ('Status Changes'[Steps]), 1, 0 ) )

Any help is appreciated, thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Hope someone can provide a more concise scheme.

step1 = 
var step = CALCULATE(MAX('Table'[created date]),FILTER('Table','Table'[candidate ID]=EARLIER('Table'[candidate ID])&&'Table'[steps]="applied"))
return
IF(ISBLANK(step),DATE(2099,1,1),step)

step2 = 
var step = CALCULATE(MAX('Table'[created date]),FILTER('Table','Table'[candidate ID]=EARLIER('Table'[candidate ID])&&'Table'[steps]="screening"))
return
IF(ISBLANK(step),DATE(2099,1,1),step)

step3 = 
var step = CALCULATE(MAX('Table'[created date]),FILTER('Table','Table'[candidate ID]=EARLIER('Table'[candidate ID])&&'Table'[steps]="offer"))
return
IF(ISBLANK(step),DATE(2099,1,1),step)

Column = IF('Table'[step1]>'Table'[step2]||'Table'[step1]>'Table'[step3]||'Table'[step2]>'Table'[step3],'Table'[candidate ID],BLANK())

Best Regards,

Jay