Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a list with unique system ID's who require periodic Preventive Maintenance. I would like to know whether for that same system ID there has been a Corrective Maintenance within one month after the Preventive Maintenance. So in the example table below, I would like to identify the case types in red, those are corrective maintenance cases that occured within a month after a preventive maintenance. How do I make this identification?
System ID | Case Type | Date |
1 | Preventive Maintenance | 01/Jan/24 |
2 | Preventive Maintenance | 01/Jan/24 |
3 | Preventive Maintenance | 01/Feb/24 |
4 | Preventive Maintenance | 01/Feb/24 |
1 | Corrective Maintenance | 25/Jan/24 |
5 | Preventive Maintenance | 01/Mar/24 |
6 | Preventive Maintenance | 01/Apr/24 |
2 | Corrective Maintenance | 02/Feb/24 |
3 | Corrective Maintenance | 17/Feb/24 |
7 | Preventive Maintenance | 01/Apr/24 |
8 | Preventive Maintenance | 01/May/24 |
9 | Preventive Maintenance | 01/May/24 |
5 | Corrective Maintenance | 02/Jun/24 |
6 | Corrective Maintenance | 08/Apr/24 |
6 | Corrective Maintenance | 12/Jun/24 |
Solved! Go to Solution.
Hi @KvO88 ,
I apologize for ignoring the extra condition, please try the following expression:
Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[Case Type]="Preventive Maintenance"&&[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30&&MAX('Table'[Case Type])="Corrective Maintenance","red","")
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @KvO88 ,
On top of that, add a measure:
Measure 2 =
SUMX(ALLSELECTED('Table'),[Measure])
Best regards,
Community Support Team_ Scott Chang
@v-tianyich-msft Thanks so much, this already helps a lot.
There is one thing that I still run in to, does the measure takes into account that I only want to see when a corrective maintenance is within a month after a preventive maintenance?
It looks like it checks all lines, despite the Case type. Thanks in advance for your help!
Hi @KvO88 ,
I apologize for ignoring the extra condition, please try the following expression:
Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[Case Type]="Preventive Maintenance"&&[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30&&MAX('Table'[Case Type])="Corrective Maintenance","red","")
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@v-tianyich-msft Thanks! This works 😃 One additional question, how can I convert this measure so that I get the counts of the number of Corrective Maintenances that occured within a month after Preventive?
Hi @KvO88 ,
Try the following expression:
Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[Case Type]="Preventive Maintenance"&&[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30&&MAX('Table'[Case Type])="Corrective Maintenance",COUNTROWS('Table'),"")
Best regards,
Community Support Team_ Scott Chang
@v-tianyich-msft Thank you for you quick replies! I really appreciate it.
If I use a Card visual with this measure, I do not get a result....
Hi @KvO88 ,
On top of that, add a measure:
Measure 2 =
SUMX(ALLSELECTED('Table'),[Measure])
Best regards,
Community Support Team_ Scott Chang
Hi @KvO88 ,
I made simple samples and you can check the results below:
Measure = var _t =ADDCOLUMNS('Table',"A",MINX(FILTER(ALL('Table'),[System ID]=EARLIER([System ID])),[Date]))
var _t2 =DATEDIFF(MINX(_t,[A]),SELECTEDVALUE('Table'[Date]),DAY)
RETURN IF(_t2>0&&_t2<30,"red","")
An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
51 | |
37 | |
20 | |
14 | |
13 |
User | Count |
---|---|
96 | |
71 | |
29 | |
20 | |
13 |