Power BI基于Azure DevOps历史数据计算阻塞时间的技术问询

Hey there! Let's work through this blocked time calculation for your Azure DevOps data in Power BI—since you're a beginner, I'll break this down step by step with clear DAX code and explanations that match your three scenarios.

Core Problem Recap #

You need to calculate total blocked time for each WorkItemId:

• When a row has the "Blocked" tag, count the time from its AnalyticsUpdatedDate until the next non-blocked row (or current date if the item is currently blocked and IsCurrent=True).
• Sum all these intervals and write the total to the BlockedTime column for the IsCurrent=True row (or the only row if there's no current record, like WorkItem 149).

Step-by-Step DAX Solution #

First, make sure your AnalyticsUpdatedDate is formatted as a DateTime type, and your table is sorted by Index (smallest = most recent revision).

Create a new calculated column named BlockedTime with this DAX code:

BlockedTime = 
VAR CurrentWorkItem = [WorkItemId]
VAR CurrentIsCurrent = [IsCurrent]
VAR CurrentDate = NOW() // Use TODAY() if you only need date, not time

-- Get all records for the current WorkItem, sorted by Index (most recent first)
VAR WorkItemRecords = 
    FILTER(
        ALL('YourTableName'), -- Replace with your actual table name
        'YourTableName'[WorkItemId] = CurrentWorkItem
    )
VAR SortedRecords = 
    ADDCOLUMNS(
        WorkItemRecords,
        "@RowRank", RANKX(WorkItemRecords, [Index],, ASC, DENSE)
    )

-- Generate unique blocked intervals (avoid double-counting consecutive blocked rows)
VAR BlockedIntervals = 
    GENERATE(
        SortedRecords,
        VAR IsBlocked = CONTAINSSTRING([TagNames], "Blocked")
        VAR CurrentUpdateDate = [AnalyticsUpdatedDate]
        
        -- Find the next non-blocked record's date (looking back in time, i.e., higher Index)
        VAR NextUnblockedDate = 
            MAXX(
                FILTER(
                    SortedRecords,
                    [@RowRank] > EARLIER([@RowRank]) && NOT CONTAINSSTRING([TagNames], "Blocked")
                ),
                [AnalyticsUpdatedDate]
            )
        
        -- Determine the end date of the blocked interval
        VAR IntervalEndDate = 
            IF(
                CurrentIsCurrent && IsBlocked,
                CurrentDate, -- Use current time if item is currently blocked
                IF(ISBLANK(NextUnblockedDate) && IsBlocked, CurrentDate, NextUnblockedDate)
            )
        
        -- Only keep the first row of each consecutive blocked segment (to avoid duplicate sums)
        RETURN
            IF(
                IsBlocked && NOT CONTAINSSTRING(LOOKUPVALUE(SortedRecords[TagNames], SortedRecords[@RowRank], [@RowRank]-1), "Blocked"),
                ROW("@Start", CurrentUpdateDate, "@End", IntervalEndDate),
                BLANK()
            )
    )

-- Calculate total blocked time by summing all interval durations
VAR TotalBlockedDuration = 
    SUMX(
        BlockedIntervals,
        DATEDIFF([@Start], [@End], SECOND) -- Swap SECOND with MINUTE/HOUR/DAY for different units
    )

-- Return total time only for IsCurrent rows or single-record WorkItems
RETURN
    IF(
        CurrentIsCurrent || COUNTROWS(WorkItemRecords) = 1,
        TotalBlockedDuration,
        BLANK()
    )

How This Works (Matching Your Scenarios) #

Let's verify this against your three examples:

Scenario 1: WorkItem 72 (IsCurrent=True, not currently blocked) #

• The code identifies two blocked segments:
  1. From 06/19/2020 (Index 1) to 06/17/2020 (Index 3, first non-blocked row after)
  2. From 06/16/2020 (Index 4) to 06/15/2020 (Index 5, first non-blocked row after)
• The sum of these intervals equals your manual calculation: (06/19-06/18)+(06/18-06/17)+(06/16-06/15)

Scenario 2: WorkItem 1 (IsCurrent=True, currently blocked) #

• Three blocked segments are calculated:
  1. From 07/07/2020 (current row) to CurrentDate
  2. From 07/06/2020 (Index9) to 07/05/2020 (Index10)
  3. From 07/04/2020 (Index11) to 07/03/2020 (Index12)
• The total matches your manual sum of these intervals.

Scenario3: WorkItem149 (only record, blocked, IsCurrent=False) #

• Since it's the only record for the WorkItem, the code uses CurrentDate - 07/02/2020 as the total blocked time, which matches your calculation.

Key Notes for Adjustments #

• Time Units: Change SECOND in DATEDIFF to MINUTE, HOUR, or DAY based on how you want to measure blocked time.
• Tag Matching: If your TagNames uses a delimiter (like semicolons for multiple tags), CONTAINSSTRING still works to detect "Blocked" anywhere in the string.
• Time Zones: If Azure DevOps returns UTC times, add CONVERT_TIMEZONE([AnalyticsUpdatedDate], "UTC", "Your Time Zone") to adjust dates to your local time.
• Performance: For large datasets, replace ALL('YourTableName') with ALLSELECTED('YourTableName') to limit the data being processed.

内容的提问来源于stack exchange，提问作者Swearengen