cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yfeng
Helper II
Helper II

calculating time from two different rows based on condition from different columns and removing rows

 

Hello PowerBi friends,

I would like to create a new column to calculate the time different ( duration ) between a "Fault" row and the "CRN_ATOEXEINS" row that is below the "Fault" row.

and to reduce data size,

removing all the "CRN_ATOEXEINS" rows that are not under "FAULT" rows.

only keeping the "Fault" rows and the "CRN_ATOEXEINS" that is underneath it.

 
 

2021-12-03_10-48-32.jpg

 

PowerBi File Link:

https://drive.google.com/drive/folders/1uGwRe2irg58XoAEBtucQVpZwDfRAvmCb?usp=sharing

1 ACCEPTED SOLUTION
yfeng
Helper II
Helper II

this is how i did it.

go the transform data

step 1 add a Index column.

step 2 do a if statement , if the previous row value is same as the current row, label "keep" if not, label "delete"

 

try if [alarm class] = #"Added Index" {[Index] - 1} [alarm class]
then "delete"
else "keep"
otherwise null

 

Step 3 filter to show only the " keep" rows.

View solution in original post

4 REPLIES 4
yfeng
Helper II
Helper II

this is how i did it.

go the transform data

step 1 add a Index column.

step 2 do a if statement , if the previous row value is same as the current row, label "keep" if not, label "delete"

 

try if [alarm class] = #"Added Index" {[Index] - 1} [alarm class]
then "delete"
else "keep"
otherwise null

 

Step 3 filter to show only the " keep" rows.

View solution in original post

lbendlin
Super User
Super User

" the "CRN_ATOEXEINS" row that is below the "Fault" row."

 

Power BI has no idea what you mean when you say "below".  Please add an index column that allows for the proper referencing.

Hi Lbendlin,

 

Yes that's what i wanted to do. but i'm not sure how i can add an index column that only identified them.

would you show me how it is done?

 

Thanks in advance

 

You can either use a suitable column in your soure data or you can add an index column in Power Query

 

lbendlin_0-1638815406665.png

 

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors