Forum Discussion
ranis1227
Helper I
Helper IDAX for LAG
Hello,
Sorry if this redundant. Please link a previous solution if applicable.
I am looking to running a LAG similar to SQL partitioned by category name and visit number, ordered by unit.
Here is an example, the highlighed transfer unit is what I am looking for:
The data is huge and SQL takes a while to load with it. Thank you again.
Hi ranis1227
Please try
Transfer Unit = VAR CurrentVisitNumTable = CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Visit_Number] ) ) VAR CurrentDateTime = TableName[Check_In_Time] VAR TableAfter = FILTER ( CurrentVisitNumTable, TableName[Check_In_Time] > CurrentDateTime ) VAR NextRecordTable = TOPN ( 1, TableAfter, TableName[Check_In_Time], ASC ) VAR Result = MAXX ( NextRecordTable, TableName[Category_Name] ) RETURN ResultGood morning ranis1227
Here is a sample file with the solution https://we.tl/t-5GlKHJIedFFrom Unit = VAR CurrentVisitNumTable = CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[visit_number ] ) ) VAR CurrentDateTime = TableName[Check_In_Time] VAR TableBefore = FILTER ( CurrentVisitNumTable, TableName[Check_In_Time] < CurrentDateTime ) VAR PreviousRecordTable = TOPN ( 1, TableBefore, TableName[Check_In_Time] ) VAR Result = MAXX ( PreviousRecordTable, TableName[To Unit] ) RETURN Result
10 Replies
- tamerj1
Community Champion
Hi ranis1227
Please try
Transfer Unit = VAR CurrentVisitNumTable = CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Visit_Number] ) ) VAR CurrentDateTime = TableName[Check_In_Time] VAR TableAfter = FILTER ( CurrentVisitNumTable, TableName[Check_In_Time] > CurrentDateTime ) VAR NextRecordTable = TOPN ( 1, TableAfter, TableName[Check_In_Time], ASC ) VAR Result = MAXX ( NextRecordTable, TableName[Category_Name] ) RETURN Result
