Home Forums Power BI Creating Categories Based on Order of Test Completion

Tagged: ,

This topic contains 11 replies, has 2 voices, and was last updated by  Jacey_Riley 2 months, 1 week ago.

Viewing 12 posts - 1 through 12 (of 12 total)
  • Author
    Posts
  • #10509

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    Hi all!

     

    I have what may be kind of a complex issue.

    I’m currently working with a data set in power query and wondering whether there was any way (within either power query, if not perhaps with a DAX formula) to create a flag that will place students in one of three categories based on the order in which they completed a test. I would have to take into consideration the date as well as time (so if a student completed an array of tests on the same date, the time would determine into which category they fell)

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

    Any suggestions would be greatly appreciated!

     

    Darko

    Attachments:
    You must be logged in to view attached files.
    #10511

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    ID 1 and ID 4 have the same sequence (Eng, PTD, Mat), but different groups, why?

    In your workbook there are 3 exams, but your example only gives the order of 3 sequences. What about PTP, Mat, Eng; Mat, Eng, PTP; Mat, PTD, Eng.

    Would it be impossible to have taken any one of the exams more than once? If so, how is that enforced?

    Would it be possible to have taken less than three exams? If so, what then?

    What version of Excel are you using?

    Tom

    #10515

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    Hi tomallan,

    I’m currently using office 365 (2016).

    Attached is an updated version of my working file. I believe I mistakenly uploaded the wrong file. My apologies!

    Attachments:
    You must be logged in to view attached files.
    #10517

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    Also, it would not be possible to have taken one of the exams more than once. It would be enforced in the data cleaning step prior to this one.

    It would be possible to have taken fewer than 3 exams.

    So if a student completed just V and Q, I would want to categorize them in “Group 1”, if they’ve only completed V and NV, “Group 2”, only Q and V “Group 3”, only Q and NV “Group 4”, NV and V “Group 5” and NV and Q “Group 6”.

    If they have only completed one test, I would want to return a “missing” label.

    Thank you again for all the help. Hope this clarifies a little more.

    #10519

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Would a blank be suitable for a “missing” label or would you want a phrase like “missing exam”?

    #10520

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    I’ve attached a final, comprehensive worksheet that includes my working set and desired result. I’ve put the possible permutations of categories in table form as well as some of the exceptions that might occur.

    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).

    I would also like to identify those that have completed only two of the three, or one of the three and label which those were.

    Thank you again

    Attachments:
    You must be logged in to view attached files.
    #10524

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    And someone who would have only completed 2 of the tests would fall into one of the 6 categories.

    For example, if a case is recorded as having 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.

    Hopefully this isn’t too convoluted.

    #10528

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    Please see the pivot on worksheet “Desired Result (Grouping)” in the attached workbook

    Attachments:
    You must be logged in to view attached files.
    #10539

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    This is fantastic. Thank you so much tomallan.

    And thank you for all the help with previous posts.

    J.

    #10542

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    I’ve just run into another issue. I’m trying to replicate your solution, but I’m getting all blanks when I insert the calculated column “Test Group” in powerpivot. The DAX/column referencing seems to be lining up, but not sure what else I could be doing wrong.

    I’ve attached a sample of where I’m hitting a bit of a wall.

    Attachments:
    You must be logged in to view attached files.
    #10545

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2545
    • Total: 2552

    When you look at the tables in diagram view, they are missing the relationship between ID columns.

    Once the relationship was added, column was populated.

    Attachments:
    You must be logged in to view attached files.
    #10553

    Jacey_Riley
    Participant
    • Started: 12
    • Replies: 14
    • Total: 26

    Got it! Thank you so much for explanation and helping to figure this out.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.