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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.