topic Find the status missing amount a line of status for different requests in DAX Commands and Tips

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

Luzadriana255 — Fri, 03 Mar 2023 20:36:44 GMT

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

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

Fowmy — Sun, 05 Mar 2023 14:42:39 GMT

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

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

Luzadriana255 — Mon, 06 Mar 2023 09:16:42 GMT

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

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

Fowmy — Mon, 06 Mar 2023 09:45:21 GMT

Luzadriana255

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

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

Luzadriana255 — Mon, 06 Mar 2023 09:49:14 GMT

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

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

Luzadriana255 — Mon, 06 Mar 2023 15:04:05 GMT

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