Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
itsme
Resolver I
Resolver I

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
Super User
Super User

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
Super User
Super User

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors