EARLIER and calculated column value return

[igaca]

I am trying to get a "ranking" based on the order of appearance of select values in a table, as determined by certain other criteria within that table (Ref. to "Dwg Nth Occurrence (IFC)" column (far right) below.)  More to the point, I want the calculated column to return a rank for each row "Activity Name" value (really a drawing number) if those values also correspond to Issue Status of "IFC".  So, in the example below, we are looking at drawing "ST-100" which was issued a number of times throughout the project, and 21 times specifically as an "IFC" version (9 other times under a different classification).  Each issuance was at a point in time, and the desire is to rank these as based on the "Issue Date" values.

The function used is as follows:

Dwg Nth Occurrence (IFC) =
IF('Dwg Log'[Issue Status]<>"IFC",BLANK(),CALCULATE([cntR-Dwg Log], FILTER('Dwg Log', 'Dwg Log'[Issue Status]="IFC" && EARLIER('Dwg Log'[Activity Name])='Dwg Log'[Activity Name] && EARLIER('Dwg Log'[Issue Date])>'Dwg Log'[Issue Date]))+1)

1st part: "IF('Dwg Log'[Issue Status]<>"IFC",BLANK(),"

I wish to not return a rank for other (non-"IFC") classifications hence the IF statement.

2nd Part: 

CALCULATE([cntR-Dwg Log], FILTER('Dwg Log', 'Dwg Log'[Issue Status]="IFC" && EARLIER('Dwg Log'[Activity Name])='Dwg Log'[Activity Name] && EARLIER('Dwg Log'[Issue Date])>'Dwg Log'[Issue Date]))

Return a count of rows (essentially an "order of appearance") for each drawing ( EARLIER('Dwg Log'[Activity Name])='Dwg Log'[Activity Name] )  as based on their temporal issuance ( && EARLIER('Dwg Log'[Issue Date])>'Dwg Log'[Issue Date]) ) and do so for only "IFC" Issue Status drawings ( 'Dwg Log'[Issue Status]="IFC" )

3rd Part:  +1

Added to account for the very first drawing issuance where there is no "previous occurrence" ( EARLIER('Dwg Log'[Issue Date])>'Dwg Log'[Issue Date] ) so that 1 is returned instead of a blank and that subsequent values take this first occurrence into account.

The Problem:

Where I run into trouble is in those instances where the same drawing ("Activity Name") was issued on the same day.  You can see this in the calc. column expression return (ref excerpt above).  I've tried adding another argument referencing the "Index" column (contains unique numerical values) along the lines of "&& EARLIER('Dwg Log'[Index])='Dwg Log'[Index]" to distinguish between those same-date instances but no luck thus far.

Any ideas?

Thanks!

IG

[danextian]

Hi @igaca,

Maybe this can help:

=
CALCULATE (
    [cntR-Dwg Log],
    FILTER ( 'Dwg Log', 'Dwg Log'[Issue Status] = "IFC" ),
    FILTER ( ALL ( 'Dwg Log'[Activity Name] ), 'Dwg Log'[Activity Name] )
        = EARLIER ( 'Dwg Log'[Activity Name] ),
    FILTER (
        ALL ( 'Dwg Log'[Issue Date] ),
        EARLIER ( 'Dwg Log'[Issue Date] ) > 'Dwg Log'[Issue Date]
    )
)

[igaca]

Dwg Nth Occurrence (IFC) =
CALCULATE([cntR-Dwg Log],
FILTER('Dwg Log','Dwg Log'[Issue Status]="IFC"),
FILTER(ALL('Dwg Log'[Activity Name]),'Dwg Log'[Activity Name])=EARLIER('Dwg Log'[Activity Name]),
FILTER(ALL('Dwg Log'),EARLIER('Dwg Log'[Issue Date])>'Dwg Log'[Issue Date]))

Looks like there is an issue with the second filter argument in that it will not allow for the EARLIER() portion to be executed as written (underscores it in red)

The error message given is "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression.  This is not allowed"

It WILL allow for the following (not modification to the 2nd FILTER argument):

Dwg Nth Occurrence (IFC) =
CALCULATE([cntR-Jedson Dwg Log],
FILTER('Jedson Dwg Log','Jedson Dwg Log'[Issue Status]="IFC"),
FILTER(ALL('Jedson Dwg Log'[Activity Name]),'Jedson Dwg Log'[Activity Name]=EARLIER('Jedson Dwg Log'[Activity Name])),
FILTER(ALL('Jedson Dwg Log'),EARLIER('Jedson Dwg Log'[Issue Date])>'Jedson Dwg Log'[Issue Date]))

That yields the following result (still short of goal):

It also returns values relative to other (non-IFC) classifications, as follows:

[igaca]

Any thoughts/ideas?

[igaca]

Let's phrase this another way: 

when using EARLIER to derive an order based on some numerical value (a date or a unique set of numbers), how would one go about breaking any ties within the "rank value" (in this instance a date value)?

[igaca]

Does anyone have any thoughts?

Again, the issue is how to break ties when ranking values based on their date order when two or more of those values have the same date.  If you think you have some ideas on how to address, please chime in.

I've tried a multitude of approaches including the use of RANKX without success.

Thanks in advance!