Forum Discussion

RicFischer's avatar
RicFischer
Helper I
5 years ago
Solved

Calculated Column - RankX with Filter

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 wh...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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's avatar
    AlB
    5 years ago

    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.

     

AlB's avatar
AlB
Community Champion
5 years ago

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's avatar
RicFischer
Helper I
5 years ago

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's avatar
    RicFischer
    Helper I
    5 years ago

    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?