having issue getting date value from prior sequence number for a job

[bsheffer]

I've been successful doing this in tsq but not as a measure

I'm trying to get the log_date for a record from the prior record in the job sequence so I can create a step duration value in minutes.  A Job is defined by the number of records that have an id and a user_name combination in this table.

this is my current measure but it always fails with a message of memory exhausted or cannot connect to the database.

prior_log_date =

var m = max('Fact Q_Log'[LOG_SEQ_NUM])

var _id = max('Fact Q_Log'[ID])

var _user_name = max('Fact Q_Log'[USER_NAME])

var a = calculate(max('Fact Q_Log'[LOG_DATE]), all('Fact Q_Log'), 'Fact Q_Log'[LOG_SEQ_NUM] <= m, 'Fact Q_Log'[ID] = _id, 'Fact Q_Log'[USER_NAME] = _user_name)

return a

is there a better way to write this as a measure so it doesn't fail?

[v-rzhou-msft]

Hi @bsheffer 

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your data model and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[bsheffer]

Sorry, but this issue has not yet been resolved due to lack of time to work on it.  I haven't tried the MAXX procedure suggested above yet.

[Greg_Deckler]

@bsheffer - Still need sample data, this might help:

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous

[bsheffer]

thanks for your help but I need this as a measure rather than a column.  The measure I created is below and I've modified it based on your post:

prior_value =

var m = max('Fact Q_Log'[LOG_SEQ_NUM])

var _id = max('Fact Q_Log'[ID])

--var a = calculate(max('Fact Q_Log'[LOG_DATE]), all('Fact Q_Log'), 'Fact Q_Log'[LOG_SEQ_NUM] < m, 'Fact Q_Log'[ID] = _id)

var a2 = maxx(filter(all('Fact Q_Log'), 'Fact Q_Log'[LOG_SEQ_NUM] < m && 'Fact Q_Log'[ID] = _id), 'Fact Q_Log'[LOG_SEQ_NUM])

return a2

I have to use all('Fact Q_Log') or the row context will limit it to just the current row.  When do that it takes forever to return and I expect it will end in error.

I used variables because I've been told they are more efficient and more transparent than Earlier()

I'm returning log_seq_num for testing purposes to make sure it is returning the right row.

[v-rzhou-msft]

Hi @bsheffer 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[bsheffer]

Unfortunately, no I still have this issue.  The code I've developed just takes far to long to finish resolving and I get an out of resource error.  Seems strange to me since I'm using variables to capture the id & current step seq num and using them to find the next earliest step seq for that user id  Should be very straight forward.  Maybe I'm just processing too many records and need to reduce the scope of the tables, but I doubt I can do that as I have to use ALL() to get what I need and I don't know of a way to reduce the size of a live dataset.

works fine in TSQL

Any input is welcome.

[v-rzhou-msft]

Hi @bsheffer 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[bsheffer]

no solution yet.  works fine in tsql but always runs out of resources in power bi.  I've provided the code.  I don't think that it can be simpler.

[bsheffer]

I was able to get this working but only for very small numbers of rows.  Same script but filtered to just a few accounts and it did return the correct values.  Not useful in most situations however.

[v-rzhou-msft]

Hi @bsheffer 

The error: out of resource may be caused by the limition of your CPU. If you use live connection to get data, you can't use Power Query Editor to transform the data model to reduce the size of your table.

Here I have some advice:

1.  You may turn off other processes in the background and try again.

2.  You may change your Measure which get result from all ID to specific ID.

3.  You can try to use Import mode and reduce size of your table and try again.

You may refer to this blog for more info to optmize the performance: Optimization guide for Power BI

Best Regards,

Rico Zhou

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

[bsheffer]

here is some sample data

ID	Log_seq_num	Expected Measure Result
164086817	25320905	blank
164086817	25320939	25320905
160398886	24116860	blank
160398886	24140072	24116860
160398886	24140134	24140072

so this table isn't formatting correctly.  Maybe this is easier to read

id                 log_seq_num  measure
164086817  25320905        blank
164086817  25320939        25320905
160398886  24116860        blank
160398886  24140072        24116860
160398886  24140134        24140072

[Greg_Deckler]

@bsheffer - Hard to say. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.