Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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,
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
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,
Glad to hear the issue is solved, you can close this thread by accepting your reply as solution.
Regards,
Lydia
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |