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

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

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