Creating Categories Based on Order of Test Completion

[Darko_Giac]

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 within power pivot?) to create a flag that will place students in one of three 2 categories based on the ordre in which they completed a test.

For example, a student who completed an English test on August 20th, Math on the 24th and Science on the 28th would be categorized as a 1.

A student who completed an English test on August 20th, [B][I]Science[/I][/B] on the 24th and Math on the 28th would be categorized as a 2.

Attached are screenshots of a workbook containing a stripped down, modified sample of my data set as well as the result I would like to achieve.

Any suggestions would be greatly appreciated.

Thank you!

Darko

[v-piga-msft · Accepted solution]

Hi @Darko_Giac,

I'm not good at power query but I think you could achieve that use dax formula.

You coud create a calculated column with the dax formual below.

Column =
IF (
    AND (
        CALCULATE (
            MAX ( 'Table1'[Test] ),
            FILTER (
                'Table1',
                'Table1'[ID] = EARLIER ( Table1[ID] )
                    && 'Table1'[Date] = MIN ( 'Table1'[Date] )
            )
        )
            = "Math",
        CALCULATE (
            MAX ( 'Table1'[Test] ),
            FILTER (
                'Table1',
                'Table1'[ID] = EARLIER ( Table1[ID] )
                    && 'Table1'[Date] = MAX ( 'Table1'[Date] )
            )
        )
            = "Science"
    ),
    1,
    IF (
        AND (
            CALCULATE (
                MAX ( 'Table1'[Test] ),
                FILTER (
                    'Table1',
                    'Table1'[ID] = EARLIER ( Table1[ID] )
                        && 'Table1'[Date] = MIN ( 'Table1'[Date] )
                )
            )
                = "Math",
            CALCULATE (
                MAX ( 'Table1'[Test] ),
                FILTER (
                    'Table1',
                    'Table1'[ID] = EARLIER ( Table1[ID] )
                        && 'Table1'[Date] = MAX ( 'Table1'[Date] )
                )
            )
                = "English"
        ),
        2
    )
)

Here is the output.

If you still want to achieve that with power query, you could get help in TechNet forum.

Best  Regards,

Cherry

[Darko_Giac]

Thanks so much! Worked exactly like I had hoped!