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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MrGriff
Frequent Visitor

Calculate Difference Between Rows

Hi I am trying to create a measure to calculate the difference between two rows, I had this as a calculated column but the data model is too big and crashes the service.

Resource Governing: This operation was canceled because there wasn’t enough memory to finish running it. Either increase the memory of the Premium capacity where this dataset is hosted or reduce the memory footprint of your dataset by doing things like limiting the amount of imported data. More details: consumed memory 25522 MB, memory limit 25522 MB, database size before command execution 77 MB. 

 

So I want to try this as a Mesure to see if this would fix the issue.

An example of the correct formate I would like is below;


Name_VALUE_TIMESTAMPCorrect Value
Tag 1158871825/02/2021 15:490
Tag 1158871825/02/2021 15:331
Tag 1158871725/02/2021 15:170
Tag 1158871725/02/2021 15:011
Tag 1158871625/02/2021 14:450
Tag 1158871625/02/2021 14:291
Tag 1158871525/02/2021 14:131
Tag 1158871425/02/2021 13:570
Tag 1158871425/02/2021 13:411
Tag 1158871325/02/2021 13:250
Tag 1158871325/02/2021 13:091
Tag 2158871825/02/2021 15:490
Tag 2158871825/02/2021 15:331
Tag 2158871725/02/2021 15:170
Tag 2158871725/02/2021 15:011
Tag 2158871625/02/2021 14:450
Tag 2158871625/02/2021 14:291
Tag 2158871525/02/2021 14:131
Tag 2158871425/02/2021 13:570
Tag 2158871425/02/2021 13:411
Tag 2158871325/02/2021 13:250
Tag 2158871325/02/2021 13:091
Tag 2158871225/02/2021 12:530
Tag 2158871225/02/2021 12:371
Tag 2158871125/02/2021 12:210
Tag 2158871125/02/2021 12:050

 

Any Help would be great 

1 ACCEPTED SOLUTION

Hi @MrGriff ,

 

You can create an index column then use the following calculated column:

 

Active Energy Delivered - Updated = 
VAR currentname = 'Fact - AE'[_NAME]
VAR currentindex = 'Fact - AE'[index]
VAR nextpower =
CALCULATE (
MAX ( 'Fact - AE'[_VALUE] ),
FILTER ( 'Fact - AE', 'Fact - AE'[_NAME] = currentname && 'Fact - AE'[index] = currentindex-1 )
)
RETURN 
IF ( NOT ISBLANK ( nextpower ), nextpower - currentpower )

 

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

 

Best Regards,

Dedmon Dai

View solution in original post

4 REPLIES 4
MrGriff
Frequent Visitor

Any Ideas anyone, still looking at the best way to do this?

 

Thanks

Greg_Deckler
Super User
Super User

@MrGriff Not sure what I am looking at in the table, are you looking to have a 1 on the earliest timestamp? In your calculated column, did you do something like MTBF? https://community.powerbi.com/t5/Quick-Measures-Gallery/Mean-Time-Between-Failure-MTBF/m-p/625082#M3...

 

Can you post your calculated column formula?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

Here is a sample of the data

https://1drv.ms/x/s!AjC_xz2zXa0Ug_c8jLCEyiol9CywhQ?e=ML8bGA

 

Active Energy Delivered - Updated = 
VAR currentname = 'Fact - AE'[_NAME]
VAR currentpower = 'Fact - AE'[_VALUE]
VAR nextpower =
CALCULATE (
MIN ( 'Fact - AE'[_VALUE] ),
FILTER ( 'Fact - AE', 'Fact - AE'[_NAME] = currentname && 'Fact - AE'[_VALUE] > currentpower )
)
RETURN 
IF ( NOT ISBLANK ( nextpower ), nextpower - currentpower )

 


But after I have looked at this it has give me the wrong value but this breaks the service, Fine on the Desktop...

Hi @MrGriff ,

 

You can create an index column then use the following calculated column:

 

Active Energy Delivered - Updated = 
VAR currentname = 'Fact - AE'[_NAME]
VAR currentindex = 'Fact - AE'[index]
VAR nextpower =
CALCULATE (
MAX ( 'Fact - AE'[_VALUE] ),
FILTER ( 'Fact - AE', 'Fact - AE'[_NAME] = currentname && 'Fact - AE'[index] = currentindex-1 )
)
RETURN 
IF ( NOT ISBLANK ( nextpower ), nextpower - currentpower )

 

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

 

Best Regards,

Dedmon Dai

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.