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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
patri0t82
Post Patron
Post Patron

Recreating DAX Custom Column in Power Query -Calculating difference in time, based on grouped values

Hello, I have a DAX custom column that works wonderful, however, this solution is not sustainable, as after roughly 24k rows, Power BI online presents memory allocation errors. It was suggested that I recreate this column in Power Query some how. Is there anyone here that can help me find a solution?
 
Much appreciated!
 
Time on Site (Minutes) (Unique Identifier) =
IF (
'Gate Scans'[Transit Direction] = "Exit",
VAR aux_ =
CALCULATE (
MAX ( 'Gate Scans'[FieldDateTime] ),
ALLEXCEPT ( 'Gate Scans', 'Gate Scans'[Company], 'Gate Scans'[SBIID1], 'Gate Scans'[Trade] ),
'Gate Scans'[FieldDateTime] < EARLIER ( 'Gate Scans'[FieldDateTime] )
)
RETURN
IF ( NOT ISBLANK ( aux_ ), 24 * 60 * ( 'Gate Scans'[FieldDateTime] - aux_ ) )
)
11 REPLIES 11
patri0t82
Post Patron
Post Patron

As I'm still receiving memory issues using this code in a calculated column, is there any way that this could be recreated as a measure to alleviate the memory issues?

Anonymous
Not applicable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndI9D4IwEIDhv0I6k3B3/UBuU2R3JwwODg6CQQb599aEAUmvsUw0JE9e6F3bqsttfA19dlS5qofH89rPGfozlAVgQUCYlUzk3zT9NM7+qbr8H1Uxll/1vk8pSOv0FCKTS255Zd2OmGHE9JhhMjtijkG4xZOsHOuD0IooyyD8VwQRLCrQqleMtisFkK78bVgb/sTzSuntbYA05ojyLdThViOralnEQCuqDCWnkOSNijHNWtiomDJspIHFmGX9M7DuAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Company = _t, Time = _t, Direction = _t, #"Time on Site" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Company", type text}, {"Time", type datetime}, {"Direction", type text}, {"Time on Site", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Time on Site"}),
   
    chk = (tab)=> if Table.First(tab)[Direction]="Exit" then Table.InsertRows(tab,0,List.Repeat({tab{0}&[Time=null]},2)) else tab,
   
    #"Raggruppate righe" = Table.Group(#"Removed Columns", {"Name", "Company"}, {{"TimeOnOff", each chk(Table.Sort(_,"Time"))}}),
    #"Removed Columns1" = Table.ExpandTableColumn(#"Raggruppate righe", "TimeOnOff", {"Time", "Direction"}, {"Time", "Direction"}),
    #"Raggruppate righe1" = Table.Group(#"Removed Columns1", {"Name", "Company","Direction"}, {{"TimeOnOff", each [Time]}}),
    #"Raggruppate righe2" = Table.Group(#"Raggruppate righe1", {"Name", "Company"}, {{"EE", each Table.FromColumns([TimeOnOff],[Direction])}}),
    #"Tabella EE espansa" = Table.ExpandTableColumn(#"Raggruppate righe2", "EE", {"Entry", "Exit"}, {"Entry", "Exit"})
in
    #"Tabella EE espansa"

Thank you for your continued help with this. Sorry for the delay over the weekend. I should add that it doesn't necessarily need to perform faster than DAX, it just needs to perform without causing Memory Allocation errors in PBI online.

 

When I tried your new code (thank you) and changed the 17:01 (5:01 PM) record to 5:01 AM, it caused three rows to have an Exit scan of 5:01 AM. There should only be one Exit scan of 5:01 AM, and the entry scan should be the next earliest time stamp, or null if there is none. 

 

I've attached a new workbook with your new code. The code you provided is the Table in the middle on the right side. I've only changed the column header from Time to Field Date Time.

 

https://drive.google.com/file/d/1lsDxPIf0GRMqPATeiYb2_Tw7kj5zswGL/view?usp=sharing 

 

Thank you again for your help through this.

Anonymous
Not applicable

If you want larger audiences you should upload a table and explain the logic to apply

Thank you for the suggestion. Here is a link to the first time I sought help on the issue.

 

https://community.powerbi.com/t5/Power-Query/Calculating-time-between-two-rows-of-datetime-stamps/m-...

Anonymous
Not applicable

Check if this implement correctly the logic you want and if perform better than script DAX.

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndI9D4IwEIDhv0I6k3B3/ZLbFNndCYODg4NgkEH+vTVhQMI1lomG5MkLvWsadbkNr77LjipXVf94Xrspw3AGXwAWBISZZ6Lwpu7GYQpP1eb/qJLRf9X7PqYgrdNTiEwuuRWUdTtihhHTY4bJ7Ig5BuEWT7JyrA9CK6LQMwg/FlEEs9qIVQtG650CSFfhOqzd/sTzQun1dYA054gKLdTbrVpW5byJG62oMpScQpJXKsY0a2GlYsqwkQYWY5b1z8DaDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Company = _t, #"Field Date Time" = _t, Direction = _t, #"Time on Site" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Company", type text}, {"Field Date Time", type datetime}, {"Direction", type text}, {"Time on Site", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Time on Site"}),
    #"Raggruppate righe" = Table.Group(#"Removed Columns", {"Name", "Company","Direction"}, {{"TimeOnOff", each [Field Date Time]}}),
    #"Raggruppate righe1" = Table.Group(#"Raggruppate righe", {"Name", "Company"}, {{"EE", each Table.FromColumns([TimeOnOff],[Direction])}}),
    #"Tabella EE espansa" = Table.ExpandTableColumn(#"Raggruppate righe1", "EE", {"Entry", "Exit"}, {"Entry", "Exit"})
in
    #"Tabella EE espansa"

 

 

 

 

Thank you so much for your solution, I think it's just about working properly. I'm running into instances in Power BI when using the code where some of the Exit scans are actually earlier than the Entry scans. 

My code is very similar to what you've provided, though, some of the column names are altered slightly. Do you know if your code accounts for the correct time stamp order?

 

Here are the lines of code I've added:

 

#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Name", "Company", "SBIID1", "Trade", "Transit Direction"}, {{"TimeOnOff", each [FieldDateTime]}}),


#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name", "Company", "SBIID1", "Trade"}, {{"EE", each Table.FromColumns([TimeOnOff],[Transit Direction])}}),


#"Expanded Entry Exit Table" = Table.ExpandTableColumn(#"Grouped Rows1", "EE", {"Entry", "Exit"}, {"Entry", "Exit"})


in
#"Expanded Entry Exit Table"

Anonymous
Not applicable

I'm not sure what you mean and what problems you find. My solution gives this result. So it doesn't depend on the order of the timestamps

 

 

 

image.png

I'm so sorry for being unclear. My apologies.

 

If you look at the data provided in that Google Drive link, you'll see a record on row 11 for Person B at 17:01 (5:01 PM).

In your screenshot above, it shows that record in row 5 to the right of 06:38, which is the way it should be. (The exit stamp should come after the entry stamp)

When I used your query with the actual data, it does not always function this way.

 

For example, in the source file from Google Drive, If I change that time from 17:01 to 5:01, (PM to AM), the record remains on that row beside 6:38, even though it's an earlier time. It should be on it's own row, above, with a null for an entry scan.

 

Please, again forgive me if this remains unclear. I'll do my best to explain further.

I've created an updated version of the file, which shows how it should function. In the new file, I've changed the 17:01 record to 5:01 AM

 

https://drive.google.com/file/d/1__LLJIFMRe2-U-bBPjt3cgn85nBz7TKa/view?usp=sharing

Anonymous
Not applicable

Ok I think I understand what the problem is. The example you have proposed, an exit without a previous entry, can be solved. But if you have to manage general cases of this type, for example entry without exit together with exit without corresponding entry, you have to put a lot of control instructions and you necessarily lose in performance. However, just to think about it, what is the most general case of unmatched entries and exits that can happen?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors