March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
Solved! Go to 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 , 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()
)
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:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |