topic Re: Check event occurred within set time of previous event of same unique ID in DAX Commands and Tips

Check event occurred within set time of previous event of same unique ID

KvO88 — Tue, 23 Apr 2024 07:40:16 GMT

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

Re: Check event occurred within set time of previous event of same unique ID

Anonymous — Wed, 24 Apr 2024 07:25:46 GMT

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.

Re: Check event occurred within set time of previous event of same unique ID

KvO88 — Thu, 25 Apr 2024 06:15:13 GMT

Anonymous 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!

Re: Check event occurred within set time of previous event of same unique ID

Anonymous — Thu, 25 Apr 2024 06:24:15 GMT

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.

Re: Check event occurred within set time of previous event of same unique ID

KvO88 — Thu, 25 Apr 2024 06:41:36 GMT

Anonymous 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?

Re: Check event occurred within set time of previous event of same unique ID

Anonymous — Thu, 25 Apr 2024 06:51:42 GMT

Hi KvO88 ,

Try the following expression:

Best regards,
Community Support Team_ Scott Chang

Re: Check event occurred within set time of previous event of same unique ID

KvO88 — Thu, 25 Apr 2024 06:58:36 GMT

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

Re: Check event occurred within set time of previous event of same unique ID

Anonymous — Thu, 25 Apr 2024 08:00:26 GMT

Hi KvO88 ,

On top of that, add a measure:

Measure 2 = SUMX(ALLSELECTED('Table'),[Measure])

Best regards,
Community Support Team_ Scott Chang