cancel
Showing results 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

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 ID Inspection Date Component Wear Status 12345 5/29/2023 2 Active 12345 3/25/2023 5 Active 12345 1/15/2023 2 Active 12345 12/22/2022 10 Inactive 12345 6/5/2022 3 Inactive 54545 4/29/2023 4 Active 54545 2/25/2023 9 Inactive
1 ACCEPTED SOLUTION
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" )
)

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.

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" )
)

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors