Forum Discussion

ddeutschman's avatar
ddeutschman
Icon for Helper I rankHelper I
8 years ago

How calculate a column based on

I need to calculate the Number of days since either   1.  Start of a Job 2.  Since the last Type of Event   There is a relationship between the Job Table and Event table.  There may be  multiple...
Need Help
Show and Tell
ddeutschman's avatar
ddeutschman
Icon for Helper I rankHelper I
8 years ago

Can I use the Related function to only return the rows in UMC Safety Incident Log where the column UMC Status = "Recordable" in the formula?  There is a direct relationship between the udJob and UMC Safety Incident Log tables.  If so, how would that look?

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi ddeutschman,

 

Could you try the formula below to see if it works in your new scenario? :smileyhappy:

Date Last Recordable =
IF (
    CALCULATE ( FIRSTNONBLANK ( 'UMC Safety Incident Log'[UMC Status], 1 ) )
        = "Recordable",
    IF (
        ISBLANK ( CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) ) ),
        udJob[StartDate],
        CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) )
    )
)

Regards

  • ddeutschman's avatar
    ddeutschman
    Icon for Helper I rankHelper I
    8 years ago

    No.  It did not return the the last Recordable Date for all Jobs.   It did for only one - 6881.  The FIRSTNONBLANK function is not finding the last entry in the data set.

     

    I have an excel file with a subset of the jobs so you can see an example of the data that is in the UMC Safety Incident Log table.   How can I upload the file?

    • ddeutschman's avatar
      ddeutschman
      Icon for Helper I rankHelper I
      8 years ago

      Here is an example of the data:

       

      UMC StatusDate of IncidentProject Number
       10/26/146262
      UMC First Aid01/21/156262
      Recordable01/27/156262
      UMC First Aid06/18/156262
      UMC First Aid09/03/156262
       10/08/156262
      Recordable01/14/156265
      Recordable07/19/166623
      Notification08/09/166623
      Notification10/03/166623
      Notification10/07/166623
       12/01/166623
      Notification05/15/176623
      Recordable06/16/176623
      UMC First Aid06/29/176623
      UMC First Aid03/02/176881
      Recordable04/21/176881
      UMC First Aid05/10/176881
      UMC First Aid06/08/177000
      • ddeutschman's avatar
        ddeutschman
        Icon for Helper I rankHelper I
        8 years ago

        Any further ideas?  If you could help me with the syntax for a function for determining the set of UMC Safety Incident Log rows that are related to the udJob row that is being evaluated, I think I could take it from there.  If there was a "Recordable" event, I am setting a Custom Column to 1.  That is working.  So I could perform a sum on the set of related rows and if > 0, then use the Max Incident Date for that related data set, otherwise set the value to the StartDate in udJob.

         

        One twist to this is that there may be a NULL set of related rows if a Recordable event has not occurred.