Calculated Column - RankX with Filter

[RicFischer]

Hi,

I have a table with the following that has date+time (ActivityStart), grade (Grade), and a concatenated field (StudentCourseStageUnit). I need to rank each identical StudentCourseStageUnit where Grade is ONLY 'S' or 'U' (and completely ignoring blank or 'I') ranked by ActivityStart.

I would like this as a calculated column.

Here's a simplistic example:

ActivityStart	StudentCourseStageUnit	Grade	AttemptNumber
2021-01-01 07:00 AM	RicCourse1Stage1Unit1	U	1
2021-01-02 01:00 PM	RicCourse1Stage1Unit1	I
2021-01-03 09:00 AM	RicCourse1Stage1Unit1	S	2
2021-01-04 06:00 AM	JonCourse1Stage1Unit1
2021-01-05 03:00 PM	JonCourse1Stage1Unit1	S	1
2021-01-07 08:30 AM	JonCourse1Stage1Unit2	S	1

Here is a sample PBIX file:

https://drive.google.com/file/d/1yKoQ7_EjUUPXJjXlMjXzLPtwPjtCmabn/view?usp=sharing

Thanks!

[AlB · Accepted solution]

Hi @RicFischer 

AttemptNumberCol = 
CALCULATE (
    COUNT ( AttemptNumber[ActivityStart] ),
    AttemptNumber[ActivityStart] <= EARLIER ( AttemptNumber[ActivityStart] ),
    AttemptNumber[Grade] IN { "S", "U" },
    ALLEXCEPT ( AttemptNumber, AttemptNumber[StudentCourseStageUnit] )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@RicFischer 

Looks good. I actually realized the other day that was initial check was missing and was surprised that it was working in all cases...

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @RicFischer 

AttemptNumberCol = 
CALCULATE (
    COUNT ( AttemptNumber[ActivityStart] ),
    AttemptNumber[ActivityStart] <= EARLIER ( AttemptNumber[ActivityStart] ),
    AttemptNumber[Grade] IN { "S", "U" },
    ALLEXCEPT ( AttemptNumber, AttemptNumber[StudentCourseStageUnit] )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[RicFischer]

It worked on my sample data, but it's taking quite a while for it to work on my real data which has nearly 500,000 rows.

[AlB · Accepted solution]

@RicFischer 

How long? Try this

Col = 
        COUNTROWS (
            FILTER (
                ALL (
                    AttemptNumber[ActivityStart],
                    AttemptNumber[Grade],
                    AttemptNumber[StudentCourseStageUnit]
                ),
                AttemptNumber[StudentCourseStageUnit]
                    = EARLIER ( AttemptNumber[StudentCourseStageUnit] )
                    && AttemptNumber[ActivityStart] <= EARLIER ( AttemptNumber[ActivityStart] )
                    && AttemptNumber[Grade] IN { "S", "U" }
            )
        )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[RicFischer]

The CALCULATE version of your solution ran out of memory at over 70gb used.

The COUNTROWS is far faster (a few seconds instead of 10+ minutes and an out-of-memory condition). But, it needs a bit of refining. It turns out if a student got an S or a U on an earlier attempt of the same unit, then a canceled unit (one with no grade) or interrupted unit (Grade = "I") is given the same attempt number as the last attempt number given.

I've updated my PBIX sample data to include an example of where that is happening. (Your formula is in the last column, "AttemptNumber".) You can find it at the original link in my OP or here:

https://drive.google.com/file/d/1yKoQ7_EjUUPXJjXlMjXzLPtwPjtCmabn/view?usp=sharing

The new student is Rob. His values are identical to where this is happening in my real data. (ActivityStart and Grade were copied from real data to sample data and the formula yielded the same false-positive.)

[RicFischer]

I think I just answered my question:

AttemptNumber = 
    IF(AttemptNumber[Grade] IN { "S", "U" },
        COUNTROWS (
            FILTER (
                ALL (
                    AttemptNumber[ActivityStart],
                    AttemptNumber[Grade],
                    AttemptNumber[StudentCourseStageUnit]
                ),
                AttemptNumber[StudentCourseStageUnit]
                    = EARLIER ( AttemptNumber[StudentCourseStageUnit] )
                    && AttemptNumber[ActivityStart] <= EARLIER ( AttemptNumber[ActivityStart] )
                    && AttemptNumber[Grade] IN { "S", "U" }
                )
        )
    )

Does that look reasonable? Any potential problems with that, @AlB?