Forum Discussion

igaca's avatar
igaca
Icon for Helper III rankHelper 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 ...
Need Help
Tutorial Requests
igaca's avatar
igaca
Icon for Helper III rankHelper 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:

 

igaca's avatar
igaca
Icon for Helper III rankHelper III
8 years ago

Any thoughts/ideas?

  • igaca's avatar
    igaca
    Icon for Helper III rankHelper III
    8 years ago

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

      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!