Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello! Our agile teams use Azure DevOps (ADO) to track their work. We've connected Power BI to ADO, to pull in data and build out custom reports in Power BI. I'm working on a report that shows average Team WIP (work in progress), by looking at when each story started and ended (or is still in progress). I found a video online that helped me with creating a chart that shows how much WIP was in progress each day, based on the Active Date and Closed Date of each story.
Relevant tables in the dataset:
Calculated column on our WorkItems table:
Any assistance would be greatly appreciated.
Solved! Go to Solution.
Glad I can help. Your measure looks good, and looking at it again I don't think the USERELATIONSHIP is necessary, because you already reference ActiveEndDate to the calendar table and then apply it to the WorkItems table. So I think you can remove this line from the code.
Have you tried applying the measure as a filter to the matrix? You can do this by setting the result equal to 1. Because the table is at WorkItem level, the result is always equal to 0 or 1, where 0 the WorkItem is not included in the range and for 1 it is.
Because you apply a measure as a filter, the filter is dynamically updated every time you make a new selection in the chart.
1. You can calculate a daily average by creating a row context at day level; calculate the number of WIP per day; and take the average of it. This can be done using the AVERAGEX function. In the first part of the function you create the row context. In this case every day of the month. Assuming your calendar table is day level and the report is filtered for the current month, you can take the VALUES from the date column in your calendar table. The expression in the AVERAGEX function is then a reference to your WIP measure. Because you refer to another measure, context transition is automatically activated and you don't have to put an explicit CALCULATE function around it. For instance:
Daily average =
AVERAGEX (
KEEPFILTERS ( VALUES ( 'Calendar_ActiveDate'[Date] ) ),
[M_WIPCountBetweenDates]
)
2. Is there an active relationship between the calendar table and Workitems table? In this case, filtering the calendar table (by selecting a specific day in the chart) would automatically filter the Workitems table, which could produce unwanted results. You could choose to set the relationship to inactive. And then activate it in measures using the USERELATIONSHIP function. This gives more freedom and the possibility to filter the report the way you want.
Thanks @Barthel! I really appreciate the guidance. I now have the chart for monthly average, per your assistance. I'm still struggling with filtering the table to show the correct results. Here is what I have.
I disabled the relationship between active date and the calendar table. I then updated this measure to include the USERRELATIONSHIP function.
M_WIPCountBetweenDates =
VAR ActiveEndDate = MAX(Dim_Calendar_ActiveDate[Calendar_Date])
VAR Result =
CALCULATE(
[M_WIPCountByActiveDate],
REMOVEFILTERS('Dim_Calendar_ActiveDate'),
USERELATIONSHIP(WorkItems[ActivatedDate_CalendarSK], Dim_Calendar_ActiveDate[CalendarSK]),
WorkItems[Work Item Type] = "User Story",
WorkItems[State] <> "Removed",
WorkItems[Activated Date] <= ActiveEndDate,
WorkItems[WIP_CloseDate] > ActiveEndDate
)
RETURN Result
I'm still stuggling connecting the dots with how I apply this to the results table that shows each user story.
Glad I can help. Your measure looks good, and looking at it again I don't think the USERELATIONSHIP is necessary, because you already reference ActiveEndDate to the calendar table and then apply it to the WorkItems table. So I think you can remove this line from the code.
Have you tried applying the measure as a filter to the matrix? You can do this by setting the result equal to 1. Because the table is at WorkItem level, the result is always equal to 0 or 1, where 0 the WorkItem is not included in the range and for 1 it is.
Because you apply a measure as a filter, the filter is dynamically updated every time you make a new selection in the chart.
@Barthel - Applying the measure as a filter on the table did the trick. I never realized you could do that. Thanks again for the assistance!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |