Forum Discussion
Helper IHow calculate a column based on
Helper ISorry, I have been out of the office and unable to test the function.
That worked but I realized that I need to base the calculation on another column, UMC Status in UMC Safety Incident Log. I tried the following but get the error that a single value for the column UMC Status can not be determined.
Date Last Recordable = IF ('UMC Safety Incident Log'[UMC Status] = "Recordable", IF(ISBLANK ( CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) ) ), udJob[StartDate], CALCULATE ( MIN ( 'UMC Safety Incident Log'[Date of Incident] ) ) ))
Thanks in advance for your assistance. I am a newbie and am still learning how to build formulas.
Helper ICan 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-msftMicrosoft Employee
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
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
Here is an example of the data:
UMC Status Date of Incident Project Number 10/26/14 6262 UMC First Aid 01/21/15 6262 Recordable 01/27/15 6262 UMC First Aid 06/18/15 6262 UMC First Aid 09/03/15 6262 10/08/15 6262 Recordable 01/14/15 6265 Recordable 07/19/16 6623 Notification 08/09/16 6623 Notification 10/03/16 6623 Notification 10/07/16 6623 12/01/16 6623 Notification 05/15/17 6623 Recordable 06/16/17 6623 UMC First Aid 06/29/17 6623 UMC First Aid 03/02/17 6881 Recordable 04/21/17 6881 UMC First Aid 05/10/17 6881 UMC First Aid 06/08/17 7000