Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the data:
And I want to show the report:
I need to know the last status (New String) of each issue until the date.
Ex: Issue A, changed status many times (30/3 changed to In Progress, 2/4 changed to Resolve, 4/4 change to Reopened, 10/4 changed to Closed.
So:
The chart will show:
30/3 -> +1 ticket (A) for status In Progress
31/1 -> 1/4 -> +1 ticket (A) for status In Progress (Because A didn't change during that time)
2/4 -> + 1 for Resolve, (No + for In Progress)...
I try 2 formulas but the performance is very slow. My idea is calculate for each status:
L_S_In Progress = VAR MaxDateView = CALCULATE ( LASTDATE ( DimDate[Date] ) ) RETURN CALCULATE ( COUNTROWS ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( dbo_jiraissue, dbo_jiraissue[id], "MAXID", CALCULATE ( MAX ( dbo_changestatus[ID] ), dbo_changestatus[ChangDate] <= MaxDateView ) ), "STATUS", CALCULATE ( VALUES ( dbo_changestatus[NEWSTRING] ), FILTER ( dbo_changestatus, dbo_changestatus[ID] = [MAXID] ) ) ), [STATUS] = "In Progress" ) ), ALL ( DimDate ) )
Chart_In Progress = VAR MaxDateView = CALCULATE ( LASTDATE ( DimDate[Date] ) ) RETURN IF ( MaxDateView = TODAY (), CALCULATE ( DISTINCTCOUNT ( dbo_jiraissue[id] ), FILTER ( dbo_jiraissue, dbo_jiraissue[issuestatus] = "3" ) ), CALCULATE ( DISTINCTCOUNT ( dbo_changestatus[issueid] ), FILTER ( ADDCOLUMNS ( SUMMARIZE ( FILTER ( ALL ( dbo_changestatus ), dbo_changestatus[ChangDate] <= MaxDateView ), dbo_changestatus[issueid], "MAXID", MAX ( dbo_changestatus[ID] ) ), "STATUS", LOOKUPVALUE ( dbo_changestatus[NEWSTRING], dbo_changestatus[ID], [MAXID] ) ), [STATUS] = "In Progress" ), ALL ( DimDate ) ) )
I try with LASTNONBLANK but the result is not correct
I try with other formula and it worked.
Can anyone take a look my formula below and help to optimize it. thanks
Changed Status = VAR MAXDATEVIEW = CALCULATE ( LASTDATE ( DimDate[Date] ) ) VAR TEMPTABLE = ADDCOLUMNS ( SUMMARIZE ( Issue , Issue[id], "MAXID", CALCULATE ( MAX ( IssueChangeCalculate[ID] ), IssueChangeCalculate[ChangDate] <= MaxDateView ) ), "STATUS", CALCULATE ( VALUES ( IssueChangeCalculate[NEWSTRING] ), FILTER ( IssueChangeCalculate, IssueChangeCalculate[ID] = [MAXID] )) ) RETURN if(VALUES('Status'[Status]) = "Open",CALCULATE ( DISTINCTCOUNT ( Issue[id] ), FILTER ( TEMPTABLE, [STATUS] = "" ),FILTER(Issue,Issue[Created Date] <= MAXDATEVIEW ), ALL ( DimDate ) ), CALCULATE ( DISTINCTCOUNT ( Issue[id] ), FILTER ( TEMPTABLE, CONTAINS ( 'Status', 'Status'[Status_Satic], [STATUS] ) ), ALL ( DimDate ) ))
HI @tanquoc0309,
Can you please share some some sample data or pbix for these to help us clarify for your scenario.
For optimize formula, you can also take a look at below link:
Optimizing duplicated DAX expressions using variables
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
My scenario: I want to know the last status of each issue until the date and then count issue based on the status
I have 3 tables below:
My expectation:
Explaining:
31-Jan: 2 issue created (Open) and no change this date -> 2 for Open and 0 for the rest status.
1-Feb: Issue 1 (ID 1) changed to "In Progress" and Issue 2 (ID 2) still "Open" -> 1 for Open and 1 for In Progress
2-Feb:
Issue 1: no change -> sill keep 1 for In Progress
Issue 2: Change to In Progress (ChangeID 5) then to Resolved (ChangeID 5) -> So Status of Issue 2 until 2-Fed: Resolved
=> 1 for In Progress and 1 for Resolved.
3-Feb, 4-Feb : No change -> still kip 1 In Progress and 1 Resolved
5-Feb:
Issue 1 goto Closed => 1 for Closed
Issue 2: no change -> 1 for resolved
==> 1 for closed & 1 for resolved.
Thanks & Best regards,
HI @tanquoc0309,
I also test with your sample data, but it seem like I can't add their nonexistent opened records.
I can only suggest to create crossjoin table with summary records.
Regards,
XIaoxin Sheng
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |