Trying to convert an Excel formula in to a customer column

[CMoppet]

Hello 🙂

I am building a PBI report to replace something that used to be just an Excel file.

I am quite proficient at writing formulae in Excel, but am struggling to figure out how to write calculations in PBI for some of the more complex operations.  I'm hoping if someone can help me convert one formula, I can learn from this to convert the others!

My raw data contains information about machine breakdowns, with each row representing one breakdown.  The rows are sorted by Serial Number ascending, and then by Created Date ascending.  I am trying to add a custom column that does the following....

IF the value in the WORK TYPE column contains the word 'Repair', see if the value in the SERIAL NUMBER column has already appeared in the data. 

If it has already appeared AND the value in the WORK TYPE column for that previous record contains the word 'Repair' or 'PM', AND the value in the FAULT LEVEL2 column IS NOT 'Cleared by Phone', count the number of days between the values in the CREATED DATE column of the previous occurrence and this occurrence. 

 The Excel formula is as follows:  =IF(AND(ISNUMBER(MATCH(I2,REPAIRONLY,0)),ISNUMBER(MATCH(LOOKUP(2,1/((F$1:F1=F2)*(AE$1:AE1<>"Cleared by Phone")),I$1:I1),RepairPM,0))),IFERROR(X2-LOOKUP(2,1/((F$1:F1=F2)*(AE$1:AE1<>"Cleared by Phone")),X$1:X1),""),"")

      where I = WORK TYPE, F = Serial Number, X = Created Date, AE = Fault Level2

Is anyone able to help me, please?

[amitchandak]

@CMoppet , Try a new column like

Days Between Repairs =
VAR CurrentSerialNumber = Breakdowns[SERIAL NUMBER]
VAR CurrentWorkType = Breakdowns[WORK TYPE]
VAR CurrentCreateDate = Breakdowns[CREATED DATE]
VAR PreviousRepairDate =
CALCULATE(
MAX(Breakdowns[CREATED DATE]),
FILTER(
ALL(Breakdowns),
Breakdowns[SERIAL NUMBER] = CurrentSerialNumber
&& Breakdowns[CREATED DATE] < CurrentCreateDate
&& (Breakdowns[WORK TYPE] = "Repair" || Breakdowns[WORK TYPE] = "PM")
&& Breakdowns[FAULT LEVEL2] <> "Cleared by Phone"
)
)
RETURN
IF(
CurrentWorkType = "Repair" && PreviousRepairDate <> BLANK(),
DATEDIFF(PreviousRepairDate, CurrentCreateDate, DAY),
BLANK()
)

[CMoppet]

Is it because the WORK TYPE fields don;t just contain the words Repair or PM?  For example, it will say UK Repair Medium, or FR Repair High.  Do I need to add an asterisk or something, to indicate that it's looking for those keywords WITHIN a text string?

[Anonymous · Accepted solution]

Hi @CMoppet 

You can use CONTAINSSTRING to identify whether a string contains a keyword, or use SEARCH (case-insensitive) or FIND (case-sensitive) to find the position of a keyword if it is contained within a string. 

If you still cannot get the expected result, can you please provide some dummy data in table format that we can work with? Currently it's difficult to understand the calculation process with only the Excel formula. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[CMoppet]

Thank you so much 🙂   This now works!

[CMoppet]

Hello,  Thanks so much for helping me.  Unfortunately, I'm not getting any results from this.  Here's what I've got:  

Effec Days = VAR CurrentSerialNumber = [Serial Number]

VAR CurrentWorkType = [Work Type: Work Type Name]

VAR CurrentCreateDate = [Created Date]

VAR PreviousRepairDate =

CALCULATE(

MAX([Created Date]),

FILTER(

ALL('WO Raw Data'),

[Serial Number] = CurrentSerialNumber

&& [Created Date] < CurrentCreateDate

&& ([Work Type: Work Type Name] = "Repair" || [Work Type: Work Type Name] = "PM")

&& [Fault Lvl 2] <> "Cleared by Phone"

)

)

RETURN

IF(

CurrentWorkType = "Repair" && PreviousRepairDate <> BLANK(),

DATEDIFF(PreviousRepairDate, CurrentCreateDate, DAY),

BLANK()

)

Is there anything obvious that I've done wrong in the above?  Sorry to be a nuisance..