Find the status missing amount a line of status for different requests

[Luzadriana255]

Hello, 

I have a 5 STEP process for different REQUESTS (A, B and C in the table). The process should be completed in the step order from 1 to 5. Every step have a STATUS NAME and a ACHIEVED STATUS column. The achieve status column should contain all the status of the steps before. I need to identify which steps where jump by returning the name of the status that are missing on the achieved status column. Request A shows the ideal case where all the status were achieved, and B and C show example of request that were missing some status in some steps. Do you have an idea on how to solve this? Thank you for your help!

Request	Step	Status Name	Achieved Status	Missing
A	1	RE
A	2	BA	RE
A	3	ST	RE, BA
A	4	WE	RE, BA,ST
A	5	PI	RE, BA,ST,WE
B	1	RE
B	2	BA	RE
B	3	ST	RE	BA
B	4	WE	RE, ST	BA
B	5	PI	RE, ST	BA,WE
A	1	RE
A	2	BA	RE
A	3	ST	RE, BA
A	4	WE	RE, BA	ST
A	5	PI	RE, BA,WE	ST

[Luzadriana255]

@Fowmy I divided the fomula into two columns and it works now. Thank you so much for your help!

[Luzadriana255]

Hi @Fowmy 

thank you so much for your Help. Your formula is great and it works perfectly in my test Data. However, it seems that my original data contains a lot of Information and I got the following Message: "there is not enough memory available for this operation. Try again later when more memory may be available"

Do you know what can be done in this case? 

Thank you so much!

Thank you

[Fowmy · Accepted solution]

@Luzadriana255 

If you want to do this using DAX then add the following Column to your table. I am not sure how big is your data table to know the performance of this DAX, please update me once you try.

Missing Step = 
VAR __REQ = [Request] 
VAR __STEP = [Step]
VAR __ACH = [Achieved Status]
VAR __MISSING = 
    CONCATENATEX(
        CALCULATETABLE(
            VALUES(Table1[Status Name]),
            Table1[Request] = __REQ,
            Table1[Step] < __STEP,
            REMOVEFILTERS('Table1')
        ),
        VAR __STATUS = Table1[Status Name] 
        VAR __EXISTS = NOT(CONTAINSSTRING(__ACH , __STATUS))
        RETURN
        IF( __EXISTS, __STATUS & ", ")
    )
VAR __RESULT = 
    IF( LEN(__MISSING) > 0 ,  LEFT(__MISSING,LEN(__MISSING)-2))
RETURN
    __RESULT

File is attached 

[Fowmy]

@Luzadriana255 

How many rows do you have in your table? 
I will also try a Power Query approach if it works for you.

[Luzadriana255]

@Fowmy I have 102960 rows. It will be great if you have an Idea on how to implement this in Power Query. Thank you!