Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RicFischer
Helper I
Helper I

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!

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @RicFischer 

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

 

SU18_powerbi_badge

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.

 

View solution in original post

@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" }
            )
        )

 

SU18_powerbi_badge

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.

 

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

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

SU18_powerbi_badge

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
Super User
Super User

Hi @RicFischer 

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

 

SU18_powerbi_badge

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.

 

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.

@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" }
            )
        )

 

SU18_powerbi_badge

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.

 

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

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.