Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
CMoppet
Helper IV
Helper IV

Trying to convert an Excel formula in to a customer column

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?
1 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!

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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()
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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?

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!

Thank you so much 🙂   This now works!

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..

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.