將狀態時間量值新增至 Power BI 報表

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

工作項目花費在特定工作流程狀態或一系列狀態的時間，是瞭解效率的重要層面。 [週期時間] 和 [ 潛在客戶時間 分析] 小工具提供一些狀態時間量值。 不過，這些小工具可能沒有您想要的詳細數據層級。

本文提供使用數據分析表示式 （DAX） 來評估工作專案在狀態組合中花費的時間的配方。 具體而言，您將瞭解如何將下列量值和計算結果列新增至Power BI報表，並使用這些數據行來產生各種趨勢圖表。 除了列出的第一個字段以外，所有欄位都是計算結果列。

計數	描述
工作項目計數 （量值）	根據工作項目的最後一天項目計算相異工作專案的計數
狀態排序順序	加入數據行，以根據 狀態類別 順序排序工作流程狀態
上一個日期	加入根據 Date 資料行計算上一個日期的數據行
日期差異天數	加入一個數據行，以計算 Date 和 Date Previous 數據行之間的天數
這是州的最後一天	加入數據行，判斷 Date 值是否為工作專案處於狀態的最後一天
天數中的狀態時間	加入一個數據行，以計算在每 一個州花費的工作專案天數
上一個狀態	加入數據行，以識別數據表中每個數據列的上一個狀態
狀態已變更	加入數據行，決定工作專案從某個狀態轉換到另一個 狀態 的日期
狀態流程	當工作專案從某個狀態轉換到另一 個狀態 時，加入說明狀態流程的數據行
狀態變更計數	加入數據行，以計算工作專案從某個狀態轉換成另一個 狀態 的次數
狀態變更計數 - 第一個完成	加入一個數據行，決定工作專案第一次轉換成已完成狀態的次數。 換句話說，當它從任何其他狀態移至 [已完成] 狀態時。
狀態變更計數 - 上次建議	加入數據行，判斷工作項目在轉換至較新的 狀態之後，是否處於建議狀態
狀態重新啟動時間以天為單位	加入一個數據行，這個數據行會計算工作項目花費在重新啟動狀態中的天數
狀態重新工作天數	加入一個數據行，以計算工作專案在已完成以外的 狀態所花費的天數

重要

• 根據本文所示的範例新增計算結果列或量值時，請將檢視名稱取代為分析檢視或數據表的數據表名稱。 例如，將檢視名稱取代為作用中的 Bug。
  
• 分析不支援日內修訂。 這些範例在參考分析檢視時使用 每日 間隔時，精確度最高。
• 計算會忽略所有日內或期間內（每周/每月）修訂。 這可能會導致特定案例產生非預期的結果，例如工作專案在一天以內未顯示「進行中」的時間。
• 盡可能使用Power BI 預設匯總，而不是建置量值。
• 某些計算包含 +0 ，以確保每個數據列包含數值，而不是BLANK。
• 您可能需要根據專案所使用的工作流程狀態來修改一些匯出數據行定義。 例如，如果您的專案使用 [新增]、 [作用中] 和 [已關閉 ] 取代 [建議]、 [進行中] 和 [已完成]。
• 本文所參考的 Date 數據行不是 Azure DevOps 中的原生數據行;這是在PowerBI中建立的衍生數據行，可協助 [狀態中的時間] 報表。 您可以使用現有的日期相關數據行來建置此數據行，例如「已變更日期」或「狀態變更日期」。

必要條件

• 存取：至少是具有基本存取權的項目成員。
• 許可權： 根據預設，項目成員具有查詢分析及建立檢視的許可權。
• 如需有關服務與功能啟用和一般數據追蹤活動之其他必要條件的詳細資訊，請參閱 存取分析的許可權和必要條件。

注意

若要練習本文所述的所有狀態量值，請務必在分析檢視、Power Query 或 OData 查詢中包含下列字段：除了預設欄位：區域路徑、指派給、反覆專案路徑、狀態、標題、工作專案標識元和工作項目類型之外，還要包含下列字段。

此外，請考慮根據 每日 數據粒度使用分析檢視。 本文中的範例是以根據自定義分析檢視在Power BI中建立作用中 Bug 報表中所定義的 Active Bugs 分析檢視為基礎，但選取 [歷程記錄] 和 [每日數據粒度] 60 天除外。 同時判斷您是否要檢閱已完成或已關閉的工作專案。

新增工作項目計數量值

為了簡化快速產生報表，我們設計了分析檢視來處理Power BI中的預設匯總。 為了說明預設匯總與量值之間的差異，我們從簡單的工作專案計數量值開始。

1. 將分析檢視載入 Power BI Desktop。 如需詳細資訊，請參閱 使用Power BI資料連接器連線、連線到分析檢視。

2. 選取資料表，然後從 功能區的 [數據表工具 ] 索引卷標[ 計算 ] 區段，選擇 [ 新增量值]。

   

3. 以下列程式代碼取代預設文字，然後選取 複選標記。

   Work Items Count=CALCULATE(COUNTROWS ('View Name'),LASTDATE ('View Name'[Date]))
   

   工作項目計數量值會使用CALCULATE本文稍後更詳細描述的、 COUNTROWS和 LASTDATE DAX 函式。

   注意

   請記得將 [檢視名稱] 取代為 [分析] 檢視的數據表名稱。 例如，在這裡，我們將檢視名稱取代為作用中 Bug。

   

量值與計算結果列有何不同

量值一律會評估整個數據表，其中計算結果列是單一數據列特有的。 如需詳細資訊，請參閱 DAX 中的匯出數據行和量值。

比較工作 專案計數 量值與根據 工作專案標識碼的預設計數匯總。 下圖是藉由將卡片視覺效果和工作專案計數量值新增至第一張卡片，並將工作專案標識符屬性新增至第二張卡片來建立。

若要使用預設匯總取得正確的計數，您可以套用篩選 為 Current 等於 'True'。 這個將篩選套用至預設匯總的模式，是本文中提供的許多範例的基礎。

新增狀態排序順序

根據預設，Power BI 會在視覺效果中依字母順序顯示狀態。 當您想要以狀態將時間可視化，且 [建議] 會顯示在 [進行中] 之後，可能會產生誤導。 下列步驟可協助解決此問題。

1. 確認 [狀態類別 ] 欄位已包含在 [分析] 檢視中。 此欄位包含在所有預設共享檢視中。

2. 選取資料表，然後從 功能區的 [數據表工具 ] 索引卷標[ 計算] 區段，選擇 [ 新增數據行]。

   

3. 以下列程式代碼取代預設文字，然後選取 複選標記。

   State Sort Order =  
   SWITCH (  
       'View Name'[State Category],  
       "Proposed", 1,  
       "InProgress", 2,  
       "Resolved", 3,  
       4  
   )  
   

   請參閱下列範例：

   

   注意

   如果您需要比 [狀態類別] 提供的更細微度，您可能需要修改定義。 不論任何狀態自定義為何，狀態類別目錄都會在所有工作項目類型之間提供正確的排序。

4. 開啟 [數據] 檢視，然後選取 [狀態] 資料行。

5. 從 [ 數據行工具] 索引標籤中，選擇 [依數據行 排序]，然後選取 [ 狀態排序順序 ] 字段。

   

新增前一個日期

計算狀態時間的下一個步驟需要針對數據集中每個數據列對應前一個間隔（日、周、月）。 這是使用計算結果列的簡單計算。 一般而言，您會定義此數據行，如下所示。

Date Previous  = 
PREVIOUSDAY ( 'View Name'[Date] )

不過，此方法有兩個主要問題：

• 它僅適用於每日期間。
• 它不會處理數據中的間距。 例如，如果工作專案在專案之間移動。

若要解決這些問題，計算結果列應該藉由掃描 [日期 ] 字段來尋找前一天。

若要新增 Date Previous 計算結果列，請從 [資料表工具] 索引卷標選擇 [新增數據行]，然後將預設文字取代為下列程式代碼，然後選取複選標記。

Date Previous =
CALCULATE (
    MAX ( 'View Name'[Date] ),
        ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
        'View Name'[Date] < EARLIER ( 'View Name'[Date] )
)

先前的日期計算結果列會使用本文稍後更詳細描述的三個 DAX 函MAX式、 ALLEXCEPT和 EARLIER。 因為數據行是計算的，所以它會針對數據表中的每個數據列執行，而且每次執行時，它都有該特定數據列的內容。

提示

從 [日期] 和 [上一個日期] 欄位的操作選單中，選擇 [日期] （而不是 [日期階層] 以查看這些字段的單一日期。

在天數中新增日期差異

Date Previous 會計算每個數據列上一個日期與目前日期之間的差異。 使用 Date Diff in Days，我們會計算每個期間之間的天數。 對於每日快照集中的大部分數據列，此值等於 1。 不過，對於數據集中有間距的許多工作專案，此值大於 1。

重要

您必須將 Date Previous 計算結果列新增至數據表。

請務必考慮數據集的第一天，其中 Date Previous 是空白的。 在此範例中，我們會為該數據列提供標準值為 1，以保持計算一致。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

Date Diff in Days =
IF (
    ISBLANK ( 'View Name'[Date Previous] ),
    1,
    DATEDIFF (
        'View Name'[Date Previous],
        'View Name'[Date],
        DAY
    )
)

此計算結果列會使用ISBLANK本文稍後所述的 和 DATEDIFF DAX 函式。

新增為狀態的最後一天

在下一個步驟中，我們會計算指定的數據列是否代表特定工作專案處於狀態的最後一天。 它支援 Power BI 中的預設匯總，我們將在下一節中新增 [日期 中的狀態時間] 資料行。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

Is Last Day in State = 
ISBLANK (CALCULATE (
    COUNTROWS ( 'View Name' ),
        ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
        'View Name'[Date] > EARLIER ( 'View Name'[Date] ),
        'View Name'[State] = EARLIER ( 'View Name'[State] )
))

在天數中新增狀態時間

工作專案在特定狀態所花費的時間現在可以計算，方法是為每個工作專案加總 Date Diff 。 此計算包含花費在特定狀態的所有時間，即使它多次切換狀態也一樣。 您可以使用日期或最新版本，將每個數據列評估為趨勢，方法是使用 [狀態為最後一天]。

重要

您必須將 [日期差異] 和 [狀態計算結果數據行的最後一天] 新增至數據表。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Time in Days = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State] = EARLIER ( 'View Name'[State] )
) + 0

根據日期中的狀態時間建立堆疊柱形圖

為了示範 [ 天 中的狀態時間] 數據行，會建立下列堆棧柱形圖。 第一張圖表顯示一段時間中每個狀態的工作項目計數。

第二張圖表說明使用中工作項目處於特定狀態的平均天數趨勢。

新增日期中的狀態時間 - 最新時間 （處於狀態的最後一天）

在評估數據表中每個工作項目的狀態，或依區域路徑等欄位篩選時，請勿在匯總中使用 [狀態時間] 資料行。 匯總會針對工作項目處於狀態的每一天使用 值。 例如，如果工作專案在星期一進行中，並在週四移至 [已完成]，則狀態中的時間是三天，但 [狀態時間] 數據行的總和為六天，1+2+3不正確。

若要解決此問題，請使用 [天數 中的狀態時間]，並套用篩選 條件為 [狀態 的最後一天] 等於 'True'。 它會排除趨勢所需的所有歷程記錄數據，並改為專注於每個狀態的最新值。

新增日期中的狀態時間 - 進行中

在先前的範例中， 指定工作專案的 [狀態 時間] 只有在工作專案處於該特定狀態時才會計算。 如果您的目標是讓給定工作專案的狀態持續計入平均值，您必須變更計算。 例如，如果我們想要追蹤「進行中」狀態，我們會在 [天數 - 進行中] 計算結果列新增 [狀態時間]。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Time in Days - In Progress = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
   'View Name'[Date] <= EARLIER('View Name'[Date]),
   'View Name'[State] = "In Progress"
) + 0

注意

您可能需要根據專案所使用的工作流程狀態來修改定義。 例如，本文範例中使用的專案會使用「進行中」工作流程狀態，不過 Agile、Scrum 和 CMMI 程式通常會使用「作用中」或「認可」狀態來代表進行中的工作。 如需概觀，請參閱 工作流程狀態和狀態類別。

下圖顯示針對每個現有工作專案（左圖所示）考慮所有處於狀態時的效果，而只考慮指定一天中處於特定狀態的工作專案（右圖所示）。

多個狀態的天數趨勢狀態時間

您也可以使用「連續」模式，分析多個狀態的效能。 不過，此方法只適用於趨勢圖。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Time in Days - Working States = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
   'View Name'[Date] <= EARLIER('View Name'[Date]),
   'View Name'[State] IN { "Committed", "In Progress" }
) + 0

注意

您可能需要根據專案所使用的工作流程狀態來修改定義。 例如，如果您的專案使用 『Active』 取代 『Committed』 或 'Proposed'。

左側圖表會顯示合併平均值，而右側則顯示每個個別狀態。

取得多個狀態的狀態時間- 多個狀態的最新狀態

您可以在建立趨勢時，使用 [日期 - 最新 ] 計算結果列的狀態時間。 使用狀態的篩選，[ 日期 中的狀態時間] 資料行和 [狀態 中的最後一天] 數據行提供簡單的方法，以取得一組狀態中花費的任何工作專案或工作專案群組的總時間。

新增上一個狀態

Date Previous 計算結果列也可以用來查閱過去的值，例如每個工作專案的先前狀態。

重要

您必須將 Date Previous 計算結果列新增至數據表。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Previous =
LOOKUPVALUE (
    'View Name'[State],
    'View Name'[Work Item Id], 'View Name'[Work Item Id],
    'View Name'[Date], 'View Name'[Date Previous]
)

此計算結果列會使用LOOKUPVALUE本文稍後所述的 。

第一個 LOOKUPVALUE 參數 'View Name'[State]會指定傳回 [State] 的值。

下一個參數 'View Name'[Work Item Id], 'View Name'[Work Item Id]會指定只考慮具有相符工作專案標識符的數據列作為目前數據列。

而且，最後一個參數 'View Name'[Date], 'View Name'[Date Previous]會指定所傳回之數據列的日期必須有符合目前數據列 [上一個日期] 的 [Date]。 在快照中，只有一個數據列可以符合此準則。

新增狀態已變更

使用 [ 狀態上一頁] 數據行，我們可以為發生狀態轉換的每個工作專案標示數據列。 [ 階段變更] 計算結果列有兩個特殊考慮：

• *State Previous 的空白值，我們設定為 工作專案的 [建立日期 ]
• 建立工作專案會被視為狀態轉換

重要

您必須將 State Previous 匯出資料行新增至資料表。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Changed =
IF (
    ISBLANK ( 'View Name'[State Previous] ),
    'View Name'[Created Date].[Date] = 'View Name'[Date],
    'View Name'[State Previous] <> 'View Name'[State]
)

計算結果列是布爾值，可識別數據列是否為狀態轉換。 藉由使用 Not Equal To 運算符，您可以正確攔截先前狀態不符合目前狀態的數據列，這表示比較會如預期般傳回 True。

新增狀態流程

使用 [狀態上 一頁] 和 [狀態變更] 計算結果列，您可以建立數據行來說明 指定工作項目的狀態流程 。 針對本文的目的，建立此數據行是選擇性的。

重要

您必須將 [狀態上一頁] 和 [狀態變更] 計算結果列新增至數據表。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Flow = 
IF([State Changed], [State Previous], [State]) & " => " & [State]

新增狀態變更計數

當我們進入更複雜的量值時，我們需要有狀態變更總數的表示法，以比較指定工作專案的數據列。 我們會藉由新增 狀態變更計數 計算結果列來取得表示法。

重要

您必須將 [狀態變更] 計算結果列新增至資料表。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Change Count = 
CALCULATE (
    COUNTROWS ( 'View Name' ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State Changed]
) + 0

新增狀態變更計數 - 最後建議的時間和狀態重新啟動時間以天為單位

狀態重新啟動時間 天是相當複雜的計算。 第一個步驟是尋找工作專案處於建議狀態的最後一次。 新增狀態變更計數 - 上次建議的計算結果列。

注意

您可能需要根據專案所使用的工作流程狀態來修改下列定義。 例如，如果您的專案使用 'New' 取代 'Proposed'。

從 [ 模型] 索引標籤標中，選擇 [ 新增] 數據行 ，然後將預設文字取代為下列程式代碼，然後選取 複選標記。

State Change Count - Last Proposed = 
CALCULATE (
    MAX ( 'View Name'[State Change Count] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State] = "Proposed"
)

然後，回顧過去，看看這個提議的狀態之前是否有一些作用中的狀態。 最後，總結工作專案在上次建議之前處於作用中狀態的所有天數。

從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

State Restart Time in Days = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State Change Count] < EARLIER('View Name'[State Change Count - Last Proposed] ),
    'View Name'[State] <"Proposed"
) + 0

由於每個數據列都會更新狀態重新啟動時間，因此您可以建立趨勢來評估特定短期衝刺的重新作業，或使用Is Current檢查個別工作專案的重新作業。

新增天數的狀態重新工作時間

與 [狀態重新啟動時間天] 類似，[ 日期 中的狀態重新工作時間] 會尋找工作專案第一次在 [已完成] 狀態類別中。 在那段時間之後，工作專案每天花費在 [已完成] 以外的狀態，會計算為重新作業。

1. 建立 [狀態變更計數 - 第一個已完成] 資料行。 此數據行會追蹤工作專案從任何其他狀態轉換為已完成狀態的次數。

   State Change Count - First Completed =
   VAR CompletedState = "Completed"
   RETURN
   CALCULATE(
      COUNTROWS('YourTable'),
      FILTER(
          'YourTable',
          'YourTable'[State] = CompletedState
          && 'YourTable'[State Change Date] = MIN('YourTable'[State Change Date])
      )
   )
   

2. 從 [ 模型] 索引標籤標中，選擇 [ 新增數據行 ]，然後將默認文字取代為下列程式代碼，然後選取 複選標記。

   State Rework Time in Days = 
   IF (
       ISBLANK ( 'View Name'[State Change Count - First Completed] ),
       0,
       CALCULATE (
           SUM ( 'View Name'[Date Diff in Days] ),
           ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
           'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
           'View Name'[State Change Count] <= EARLIER ( 'View Name'[State Change Count - First Completed] ),
           'View Name'[State] IN {"Completed", "Closed", "Cut" } = FALSE()
       ) + 0
   )
   

   注意

   您可能需要根據專案所使用的工作流程狀態來修改先前的定義。 例如，如果您的專案使用 Done 取代 Closed，依此方式。

DAX 函式

本節提供用於建立本文中新增之導出數據行和量值之 DAX 函式的其他資訊。 另 請參閱 DAX 時間智慧函式。

函式	描述
ALLEXCEPT	拿掉資料表中的所有內容篩選，但套用至指定資料行的篩選條件除外。 基本上， ALLEXCEPT ('View Name'', 'View Name'[Work Item Id]) 將數據表中的數據列縮減為只共用與目前數據列相同的工作專案標識碼的數據列。
CALCULATE	此函式是幾乎所有範例的基礎。 基本結構是表達式，後面接著套用至表達式的一系列篩選。
COUNTROWS	此函式 COUNTROWS ( 'View Name' )只會計算套用篩選之後所保留的數據列數目。
DATEDIFF	傳回兩個日期之間交叉的間隔界限計數。 DATEDIFF會減去 [日期之前的日期]，以判斷兩者之間的天數。
EARLIER	傳回所指定資料行在提及資料行外部評估行程中的目前值。 例如， 'View Name'[Date] < EARLIER ( 'View Name'[Date] ) 進一步將數據集縮減為僅使用 函式參考 EARLIER 之目前數據列日期之前發生的數據列。 EARLIER 不會參考先前的日期;它特別定義匯出資料行的數據列內容。
ISBLANK	檢查某值是否為空白，並傳回 TRUE 或 FALSE。 ISBLANK評估目前的數據列，以判斷 Date Previous 是否有值。 如果沒有，If 語句會將 Days 中的 Date Diff 設定為 1。
LASTDATE	例如，我們會將 LASTDATE 篩選套用至表達式， LASTDATE ( 'View Name'[Date] )以尋找數據表中所有數據列的最新日期，並排除未共用相同日期的數據列。 使用分析檢視所產生的快照集數據表，此篩選會有效地挑選所選期間的最後一天。
LOOKUPVALUE	針對符合search_columnName和search_value指定之所有準則的數據列，傳回 result_columnName 中的值。
MAX	傳回一個資料行中或兩個純量運算式之間的最大數值。 我們會套用 MAX ( 'View Name'[Date] )，以判斷套用所有篩選之後的最新日期。

相關文章

• 瞭解 Power BI 整合
• 建立分析檢視
• 開始使用 Power BI Desktop
• 設計 Power BI 連接器的數據集
• 瞭解工作流程狀態和狀態類別
• 探索分析的數據模型