Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to flag duplicate values but only from the first duplicate value that type.
So Far I can get all of the duplicates to be flagged using:
Column =
Var Equipment = [Fault Equipment]
Return Calculate(If(COUNTROWS(Sheet1)>1,"Yes","No"),All(Sheet1),Sheet1[Fault Equipment]=Equipment)
My challenge comes in where I try to first integrate the date into this, i have tried using as the second filter argument of
Sheet1[FaultDate]=Calendar(Earlier(Sheet1[FaultDate].[Date]),Earlier(Sheet1[FaultDate].[Date]))
I also tried using the DatesBetween function, but just can not seem to get it to even compile. Having tried using the DateAdd function as well as manually decomposing and re-composing the date values
DatesBetween((Sheet1[FaultDate]),DateAdd(Earlier(Sheet1[FaultDate]),-30,DAY),Earlier(Sheet1[FaultDate])))
DatesBetween((Sheet1[FaultDate]),Date(Year(Earlier(Sheet1[FaultDate])),Month(Earlier(Sheet1[FaultDate]))-1,Day(Earlier(Sheet1[FaultDate]))),Earlier(Sheet1[FaultDate])))
FaultRef | FaultDate | Fault Equipment | Repeat | Repeat Date |
1 | 2021/02/06 | A | No | - |
2 | 2021/02/17 | B | No | - |
3 | 2021/02/26 | C | No | - |
4 | 2021/02/27 | A | Yes | 2021/02/06 |
5 | 2021/03/03 | D | No | - |
6 | 2021/03/08 | P | No | - |
7 | 2021/03/12 | D | Yes | 2021/03/03 |
8 | 2021/03/24 | C | Yes | 2021/02/26 |
9 | 2021/04/06 | A | No | - |
10 | 2021/04/10 | E | No | - |
I attached an example table to show what I would want the output to be in the last two columns
Take note that Line 1 and 3 should not be flagged as that would be the first entries. Also Line 9 is not flagged as it is more that 30 days since the previous entry.
Any hints/guidance on where I am going wrong would be highly appreciated.
Solved! Go to Solution.
Can you try this column please
ColumnX =
Var Equipment = Sheet1[Fault Equipment]
VAR _fDate = Sheet1[FaultDate]
VAR _dateOfDup = CALCULATE(MAX(Sheet1[FaultDate]), FILTER(Sheet1, Sheet1[Fault Equipment] = Equipment && Sheet1[FaultDate] < _fDate))
RETURN
IF(DATEDIFF(_dateOfDup, _fDate, DAY) < 30, _dateOfDup)
Hi @Anonymous
Have you tried @HotChilli 's formula? It should work for Repeat Date column. For Repeat Flag column, I modify it into below one
Flag =
Var Equipment = Sheet1[Fault Equipment]
VAR _fDate = Sheet1[FaultDate]
VAR _dateOfDup = CALCULATE(MAX(Sheet1[FaultDate]), FILTER(Sheet1, Sheet1[Fault Equipment] = Equipment && Sheet1[FaultDate] < _fDate))
VAR _dateDiff = DATEDIFF(_dateOfDup, _fDate, DAY)
RETURN
IF(_dateDiff < 30 && NOT(ISBLANK(_dateDiff)), "Yes", "No")
Best Regards,
Community Support Team _ Jing
Can you try this column please
ColumnX =
Var Equipment = Sheet1[Fault Equipment]
VAR _fDate = Sheet1[FaultDate]
VAR _dateOfDup = CALCULATE(MAX(Sheet1[FaultDate]), FILTER(Sheet1, Sheet1[Fault Equipment] = Equipment && Sheet1[FaultDate] < _fDate))
RETURN
IF(DATEDIFF(_dateOfDup, _fDate, DAY) < 30, _dateOfDup)
Hi Thanks,
This works perfectly!!!
Just for my own information, in the Calculate function, in the filter, which one of the arguments is referring to the "current" row for the calculation? Would it be the Sheet1[Fault Equipment] or the variable that was declared?
Then as an added thought, would it be possible to do this in a Measure instead of a calculated column?