Creating Categories Based on Order of Test Completion

[GiacTorrence]

Hi all!



I have what may be kind of a complex issue.

I’m currently working with a data set and wondering whether there was a DAX formula that will create a flag that will place students in one of six categories based on the order in which they completed a test.

Essentially I would like to have 6 primary groupings that will occur most frequently in the data (Group 1 – Completed V, then Q, then NV; Group 2 – Completed V, then NV then Q; Group 3 – Completed Q, then V then NV; Group 4 – Completed Q, then NV then V; Group 5 – Completed NV, then V then Q; and Group 6 – Completed NV, then Q then V).

The order of test completion is based on which date/time they took one of the three tests.

If someone completed only two tests (for example completed V, then NV, they would fall into group 2, as we know that the only next possible test they will take is going to be Q. The same follows for the other 5 possible groupings).

If they only completed one test, I would like to identify them as having "Only completed X".

I have attached a secreenshot of the workbook with anonymized data as well as the desired result which will (hopefully) be a clearer reference.

Working set:
https://i.stack.imgur.com/Z49Yg.png

Desired Result:
https://i.stack.imgur.com/xStN2.png

Any suggestions would be greatly appreciated.

 

[Fluff]

Cross posted https://stackoverflow.com/questions...-categories-based-on-order-of-test-completion

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.