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 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:

ActivityStartStudentCourseStageUnitGradeAttemptNumber
2021-01-01 07:00 AMRicCourse1Stage1Unit1U1
2021-01-02 01:00 PMRicCourse1Stage1Unit1I 
2021-01-03 09:00 AMRicCourse1Stage1Unit1S2
2021-01-04 06:00 AMJonCourse1Stage1Unit1  
2021-01-05 03:00 PMJonCourse1Stage1Unit1S1
2021-01-07 08:30 AMJonCourse1Stage1Unit2S1

 

Here is a sample PBIX file:

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

 

Thanks!

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.

     

6 Replies

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

         

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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.