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
PowerBI123456
Post Partisan
Post Partisan

Checking if a deleted value was added onto another line

Hi, would appreciate some help. I am trying to figure out how to see if a charge that was deleted was added to another charge. For example, Charge 1 is 5, and then it was deleted. It was then added onto charge 2, you can tell since charge 2 was originally 10 but it was updated to 15. Same thing with Charge 3, its originally 3, then deleted. You can see that Charge 6 went from 9 to 12 (difference of 3). Hope that makes sense. I am dealing with 100,000s of rows, so needs to be optimal. Thanks!

PowerBI123456_1-1593550190849.png

 

1 ACCEPTED SOLUTION

@PowerBI123456 

if I understand you correct try a column

Added = 
var _isUpdatedAfter = CALCULATE(COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[ID]), 'Table'[Step] = "Updated" && 'Table'[Charges] > EARLIER('Table'[Charges])))
RETURN
IF([Step] = "Line deleted" && _isUpdatedAfter > 0, "yes", "no")

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

10 REPLIES 10
az38
Community Champion
Community Champion

hi @PowerBI123456 

so, whats your desired output? how should look like result?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38  Hi, trying to see if a deleted charge was added to another charge. I updated below, so charge 7 was deleted, but was not added to another charge. Hope that makes sense.

 

PowerBI123456_0-1593552079487.png

 

@az38  Forgot to mention there are certain ID to factor in. So for example, ID 2 has a charge that was updated from 5 to 7 (difference of 2), but that can't be from charge 7 being deleted from ID 1. 

 

PowerBI123456_0-1593552466905.png

 

Hi @PowerBI123456 ,

 

Could the method @az38 posted meet your requirements?

If it could, please accept his reply as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

 

In addition, is it necessary to judge whether the difference is the same as the deleted line value? What if they are not the same?

 

 

Best Regards,

Icey

 

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

@Icey yes, the difference needs to be exactly the same. 

@az38  thank you so much! It looks like it worked. 

@az38  Could you please help me understand why you wrote the variable out the way you did? Just trying to understand it better., specifically the ">Earlier" part.  Thanks!!!

@PowerBI123456 

EARLIER() in calculated column refers to the currrent row value. so, we need to define if there is exists a row after current with given conditions


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38thank you!!!

 

I am testing it out now, and also need to verify against a system. I will let you know once I am done, thanks again!!! 

@PowerBI123456 

if I understand you correct try a column

Added = 
var _isUpdatedAfter = CALCULATE(COUNTROWS('Table'), FILTER(ALLEXCEPT('Table', 'Table'[ID]), 'Table'[Step] = "Updated" && 'Table'[Charges] > EARLIER('Table'[Charges])))
RETURN
IF([Step] = "Line deleted" && _isUpdatedAfter > 0, "yes", "no")

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.

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