Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello BI-Pros,
I have an extensive data set of issues that are created, edited, resolved, reactivated, and finally closed over a period of time. Changes are made by different editors and sometimes several times a day.
If an issue ist not resolved or closed then it is active. Here is the example data for issue #54:
Index | ChangedBy | TimeStamp | IssueAction |
54 | Person A | 14.10.2020 06:37:34 | Created |
54 | Person A | 14.10.2020 07:13:06 | Edited |
54 | Person B | 11.11.2020 09:40:25 | Resolved |
54 | Person A | 24.11.2020 18:29:59 | Reactivated |
54 | Person B | 25.11.2020 09:51:39 | Resolved |
54 | Person A | 08.12.2020 10:27:16 | Edited |
54 | Person A | 08.12.2020 14:27:10 | Edited |
54 | Person A | 21.01.2021 10:57:26 | Closed |
Now I want to have a cumulative stacked area chart like that:
I want to see the pure number of issues (cumulative) over the time and in what proportions these are resolved or active. To do this, I need the exact status for each day (active, closed, resolved), even if on some days no status change is recorded for one of the issues.
I am having difficulty with two issues:
1. accumulation of the total number of issues
2. finding out which status the issues had on the corresponding days
Can you help me further?
Thanks a lot
Julia
Hey @DataInsights,
wow! It takes forever to update because of the enormous amount of data, but I'll take that.
On 04/24/2021, however, the number of active and closed issues suddenly seems to turn around:
From this day on, the graph no longer corresponds to reality. Do you have any idea why?
Thanks a lot,
Julia
Regarding performance, you might try the following:
1. Create a table IssuesMaster with one row per issue. You can do this in Power Query or a calculated table.
2. Create a one-to-many relationship between IssuesMaster and Issues.
3. Use the pattern below for each measure. This approach iterates IssuesMaster (distinct list of issues), rather than calculating the distinct list of issues dynamically and iterating this list.
Active =
VAR vCurrentDate =
MAX ( DimDate[Date] )
VAR vResult =
SUMX (
IssuesMaster,
VAR vCurrentIssue = IssuesMaster[Index]
VAR vTableCurrentIssue =
FILTER (
ALLSELECTED ( Issues ),
Issues[Index] = vCurrentIssue
&& Issues[TimeStamp] <= vCurrentDate
)
VAR vCurrentTimestamp =
CALCULATE ( MAX ( Issues[TimeStamp] ), vTableCurrentIssue )
VAR vCurrentStatus =
CALCULATE (
MAX ( Issues[IssueAction] ),
Issues[TimeStamp] = vCurrentTimestamp,
vTableCurrentIssue
)
RETURN
IF (
NOT ISBLANK ( vCurrentStatus )
&& NOT vCurrentStatus IN { "Resolved", "Closed" },
1
)
)
RETURN
vResult
Regarding the data discrepancy on 04/24/2021, I would need to see the underlying data. Would you be able to provide a link to the pbix file? You can scale it down so that only the necessary rows are included.
Proud to be a Super User!
Try these measures. The data model needs a date table (DimDate) with a relationship to the data table.
Active =
VAR vCurrentDate =
MAX ( DimDate[Date] )
VAR vIssues =
CALCULATETABLE ( VALUES ( Issues[Index] ), ALLSELECTED ( Issues ) )
VAR vResult =
SUMX (
vIssues,
VAR vCurrentIssue = Issues[Index]
VAR vTableCurrentIssue =
FILTER ( ALLSELECTED ( Issues ), Issues[Index] = vCurrentIssue )
VAR vCurrentTimestamp =
CALCULATE (
MAX ( Issues[TimeStamp] ),
Issues[TimeStamp] <= vCurrentDate,
vTableCurrentIssue
)
VAR vCurrentStatus =
CALCULATE (
MAX ( Issues[IssueAction] ),
Issues[TimeStamp] = vCurrentTimestamp,
vTableCurrentIssue
)
RETURN
IF (
NOT ISBLANK ( vCurrentStatus )
&& NOT vCurrentStatus IN { "Resolved", "Closed" },
1
)
)
RETURN
vResult
Resolved =
VAR vCurrentDate =
MAX ( DimDate[Date] )
VAR vIssues =
CALCULATETABLE ( VALUES ( Issues[Index] ), ALLSELECTED ( Issues ) )
VAR vResult =
SUMX (
vIssues,
VAR vCurrentIssue = Issues[Index]
VAR vTableCurrentIssue =
FILTER ( ALLSELECTED ( Issues ), Issues[Index] = vCurrentIssue )
VAR vCurrentTimestamp =
CALCULATE (
MAX ( Issues[TimeStamp] ),
Issues[TimeStamp] <= vCurrentDate,
vTableCurrentIssue
)
VAR vCurrentStatus =
CALCULATE (
MAX ( Issues[IssueAction] ),
Issues[TimeStamp] = vCurrentTimestamp,
vTableCurrentIssue
)
RETURN
IF ( vCurrentStatus = "Resolved", 1 )
)
RETURN
vResult
Closed =
VAR vCurrentDate =
MAX ( DimDate[Date] )
VAR vIssues =
CALCULATETABLE ( VALUES ( Issues[Index] ), ALLSELECTED ( Issues ) )
VAR vResult =
SUMX (
vIssues,
VAR vCurrentIssue = Issues[Index]
VAR vTableCurrentIssue =
FILTER ( ALLSELECTED ( Issues ), Issues[Index] = vCurrentIssue )
VAR vCurrentTimestamp =
CALCULATE (
MAX ( Issues[TimeStamp] ),
Issues[TimeStamp] <= vCurrentDate,
vTableCurrentIssue
)
VAR vCurrentStatus =
CALCULATE (
MAX ( Issues[IssueAction] ),
Issues[TimeStamp] = vCurrentTimestamp,
vTableCurrentIssue
)
RETURN
IF ( vCurrentStatus = "Closed", 1 )
)
RETURN
vResult
Here are the results in a matrix. DimDate[Date] is used in both the date slicer and the matrix rows.
Sample data:
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |