EARLIER function not working using DAX query

[Anonymous]

I need to get Next Row value, my steps are following:
1) Creating [RankIndex] column as RANKX grouping [IssueID] by 2 ASC columns [StartTime], [EndTime];
2) On this step using EARLIER function on [RankIndex] and [IssueID] column to get next row value.

Issue is that result is NULL, feeling is that EARLIER is not working on created column within variables table.

If I try to reproduce on dummy data in Report mode using the same steps in variables, then everything is working.
Please help, code:

let
 Source = AnalysisServices.Database(
 "xxxxxxxxxxxxxxxx", 
 "yyyyyyyyyyyyyyyy", 
 [Query="
 DEFINE
 var status_table = " & dax_query_OtherTables("USED_TABLE")
 & "
 var issue_table = SELECTCOLUMNS(" & #"dax_query_JiraIssue" & ", ""IDD"", VALUE([ID]), ""IssueKey"", [Key], ""IssueCurrentStatus"", [Status], ""IssueProjectKey"", [ProjectKey] )
 var join_table = NATURALINNERJOIN(status_table, issue_table)
 var table_add_columns = ADDCOLUMNS(
 ADDCOLUMNS( join_table , ""IssuesFlaggedStatus"", CALCULATE( 
 CONTAINS(USED_TABLE, [Status], ""Impediment""), 
 FILTER( USED_TABLE, EARLIER(USED_TABLE[IssueID]) = USED_TABLE[IssueID])
 ),
 ""RankIndex"", IF( USED_TABLE[Status] <> ""Impediment"",
 RANKX( FILTER(USED_TABLE, USED_TABLE[IssueID] = EARLIER( USED_TABLE[IssueID] ) && USED_TABLE[Status] <> ""Impediment""), 
 RANKX( FILTER(USED_TABLE, USED_TABLE[IssueID] = EARLIER( USED_TABLE[IssueID] ) && USED_TABLE[Status] <> ""Impediment""), USED_TABLE[StartTime], , DESC)
 + DIVIDE(
 RANKX( FILTER(USED_TABLE, USED_TABLE[IssueID] = EARLIER( USED_TABLE[IssueID] ) && USED_TABLE[Status] <> ""Impediment""), USED_TABLE[EndTime], , DESC),
 ( COUNTROWS( FILTER(USED_TABLE, USED_TABLE[IssueID] = EARLIER( USED_TABLE[IssueID] ) && USED_TABLE[Status] <> ""Impediment"") ) + 1 ) 
 )
 )
 
 )
 ), ""NextStatus"", CALCULATE( 
 MAX( USED_TABLE[Duration] ), 
 FILTER( USED_TABLE, EARLIER(USED_TABLE[IssueID]) = USED_TABLE[IssueID]
 && EARLIER( [RankIndex] )+1 = [RankIndex]
 )
 ))
 EVALUATE table_add_columns" 
 , Implementation="2.0"])
in
 Source

Result:

[Anonymous]

@Anonymous,

When you use SQL Server Management Studio to connect to SSAS and implement the above query with variables, do you get expected result?


Regards,
Lydia

[Anonymous · Accepted solution]

Unfortunatelly I do not have access SQL Server Management Studio as I am end user, who creates report on Cube data.

But it seems I solved an issue by doing ranking and finding next row value in power query.

[Anonymous]

@Anonymous,

Glad to hear the issue is solved, you can close this thread by accepting your reply as solution.

Regards,
Lydia