cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
itsme
Advocate II
Advocate II

Mark "Active" for all rows since the last time a value was 9 or greater by ID

I need to do this as a calculated column and there is no date table - I know we need a date table, but I'm taking over this dataset and will build it properly, until then, I can't add a date table or else many calculations break. We have machine ID numbers and dates that they have been inspected. For each date, we measure the size of the padding on some components on the machine. Everytime the padding increases by 9 inches or more, we know the component has been replaced in the next inspection date. There's a column that identifies if the wear has been 9 inches or more. Machines can have components replaced multiple times. I need to identify the last time the component was replaced - so the inspection after the last time the wear was 9+ inches - and mark each inspection since then as "Active" and the others as "Inactive". I need to do this for each machine ID number.

 

Below is an example of the data I have so far. The "Status" column is what I need to solve for. Can anyone please help me solve this?

 

Machine IDInspection DateComponent WearStatus
123455/29/20232Active
123453/25/20235Active
123451/15/20232Active
1234512/22/202210Inactive
123456/5/20223Inactive
545454/29/20234Active
545452/25/20239Inactive
1 ACCEPTED SOLUTION
NaveenGandhi
Continued Contributor
Continued Contributor

Hello @itsme 

Try the below Dax.

Status =
VAR Inspection_9 =
CALCULATE (
MAX ( 'Component Active'[Inspection Date] ),
'Component Active'[Component Wear] >= 9,
ALLEXCEPT ( 'Component Active', 'Component Active'[Machine ID] )
)
RETURN
IF (
AND (
'Component Active'[Component Wear] < 9,
'Component Active'[Inspection Date] > Inspection_9
),
"Active",
IF ( 'Component Active'[Inspection Date] <= Inspection_9, "Inactive" )
)
 

NaveenGandhi_2-1685429319481.png

 

Let me know if you have any questions or issues.

 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 

View solution in original post

1 REPLY 1
NaveenGandhi
Continued Contributor
Continued Contributor

Hello @itsme 

Try the below Dax.

Status =
VAR Inspection_9 =
CALCULATE (
MAX ( 'Component Active'[Inspection Date] ),
'Component Active'[Component Wear] >= 9,
ALLEXCEPT ( 'Component Active', 'Component Active'[Machine ID] )
)
RETURN
IF (
AND (
'Component Active'[Component Wear] < 9,
'Component Active'[Inspection Date] > Inspection_9
),
"Active",
IF ( 'Component Active'[Inspection Date] <= Inspection_9, "Inactive" )
)
 

NaveenGandhi_2-1685429319481.png

 

Let me know if you have any questions or issues.

 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors