Forum Discussion

igaca's avatar
igaca
Helper III
8 years ago

EARLIER and calculated column value return

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

Need Help
Tutorial Requests

5 Replies

  • danextian's avatar
    danextian
    Super User
    8 years ago

    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's avatar
      igaca
      Helper III
      8 years ago

       

      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: