Power BI レポートに状態の時間メジャーを追加する
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019
作業項目が特定のワークフロー状態または一連の状態で費やす時間は、効率を理解するための重要な側面です。 サイクル時間とリード タイム分析ウィジェットには、状態の時間のメジャーがいくつか用意されています。 ただし、これらのウィジェットには、必要な詳細レベルがない可能性があります。
この記事では、データ分析式 (DAX) を使用して、状態の組み合わせで作業項目によって費やされた時間を評価するレシピを提供します。 具体的には、次のメジャーと計算列を Power BI レポートに追加し、それらを使用してさまざまな傾向グラフを生成する方法について説明します。 すべてのフィールドは、最初に一覧表示されるフィールドを除く集計列です。
Count | 説明 |
---|---|
作業項目数 (メジャー) | 作業項目の最終日エントリに基づいて個別の作業項目の数を計算します |
状態の並べ替え順序 | 状態カテゴリ シーケンスに基づいてワークフローの状態を並べ替えるために使用する列を追加します |
前の日付 | Date 列に基づいて前の日付を計算する列を追加します |
日付の差分 (日数) | Date 列と Date Previous 列の間の日数を計算する列を追加します |
状態の最後の日 | Date 値が、作業項目が State の最後の日かどうかを決定する列を追加します。 |
状態時間 (日) | 各 State で作業項目が費やされた日数を計算する列を追加 します |
前の状態 | データ テーブルの各行の前の状態を識別する列を追加します |
状態の変更 | ある状態から別の 状態 に作業項目が切り替わる日付を決定する列を追加します。 |
状態フロー | ある状態から別の状態に作業項目が遷移する状態フローを示す 列を追加 します |
状態の変更数 | ある状態から別の 状態 に作業項目が切り替わる回数を計算する列を追加します |
状態変更数 - 最初の完了 | 作業項目が最初に完了状態に遷移する回数を決定する列を追加します。 つまり、他の状態から Completed 状態に移行する場合です。 |
状態変更数 - 最後に提案された | 作業項目が後の状態に移行した後、以前に提案された状態にあったかどうかを決定する列を追加 します |
状態の再起動時間 (日数) | 作業項目が再起動状態で費やされた日数を計算する列を追加します |
状態のリワーク時間 (日数) | 完了以外の状態で作業項目が費やした日数を計算する列を追加 します |
重要
- この記事に示す例に従って計算列またはメジャーを追加する場合は、 ビュー名 を分析ビューまたはデータ テーブルのテーブル名に置き換えます。 たとえば、[ ビュー名] を[アクティブなバグ] に置き換えます。
- 分析では、日内リビジョンはサポートされていません。 これらの例は、Analytics ビューを参照するときに 日単位 の間隔を使用する場合に最も精度が高い値を持ちます。
- 計算では、すべての日内または期間内 (毎週/毎月) のリビジョンは無視されます。 これにより、作業項目が 1 日未満の "進行中" の場合に時間 "進行中" が表示されない作業項目など、特定のシナリオで予期しない結果が発生する可能性があります。
- Power BI の既定の集計は、メジャーを構築する代わりに可能な限り使用されます。
- 一部の計算には +0 が含まれており、空白ではなく行ごとに数値が含まれるようにします。 プロジェクトで使用されるワークフローの状態に基づいて、計算列の定義の一部を変更する必要がある場合があります。 たとえば、プロジェクトで [提案]、[進行中]、[完了] の代わりに [新規]、[アクティブ]、[クローズ] を使用している場合です。
前提条件
- アクセス レベル: Basic 以上のプロジェクトのメンバーである必要があります。
- Permission: 既定では、プロジェクト メンバーは Analytics にクエリを実行してビューを作成する権限を持ちます。
- サービスと機能の有効化と一般的なデータ追跡アクティビティに関するその他の前提条件の詳細については、「 Analytics にアクセスするためのアクセス許可と前提条件を参照してください。
Note
この記事で説明するすべての状態時間メジャーを実行するには、分析ビュー、Power Query、または OData クエリに、既定のフィールドに加えて[作成日と状態カテゴリ]、[エリア パス]、[割り当て先]、[イテレーション パス]、[状態]、[タイトル]、[作業項目 ID]、[作業項目の種類] の各フィールドを含めるようにします。
また、 日単位 の細分性に基づく分析ビューの使用も検討してください。 この記事の例は、「カスタム分析ビューに 基づいて Power BI でアクティブなバグレポートを作成する」で定義されている Active Bugs Analytics ビューに基づいています。例外として、60 日間の 履歴 と 日単位 の細分性が選択されています。 また、完了した作業項目と終了した作業項目を確認するかどうかを決定します。
作業項目数メジャーを追加する
レポートの生成をすばやく簡単にするために、Power BI で既定の集計を操作するように Analytics ビューを設計しました。 既定の集計とメジャーの違いを示すために、まず単純な作業項目数メジャーから始めます。
Power BI Desktopに Analytics ビューを読み込みます。 詳細については、「 Power BI Data Connector を使用して接続する」、「分析ビューに接続する」を参照してください。
データ テーブルを選択し、リボンの [ テーブル ツール ] タブの [ 計算 ] セクションから [ 新しいメジャー] を選択します。
既定のテキストを次のコードに置き換え、チェックマークを
選択します。
Work Items Count=CALCULATE(COUNTROWS ('View Name'),LASTDATE ('View Name'[Date]))
作業項目数メジャーでは、この記事で
CALCULATE
後述する 、COUNTROWS
、およびLASTDATE
DAX 関数を使用します。Note
ビュー名は、Analytics ビューのテーブル名に置き換えてください。 たとえば、ここでは ビュー名 を アクティブなバグに置き換えます。
メジャーと計算列の違い
メジャーは、計算列が 1 つの行に固有のテーブル全体を常に評価します。 詳細については、「DAX の 計算列とメジャー」を参照してください。
作業項目数メジャーを、作業項目 ID に基づく既定のカウント集計と比較します。 次の図は、 カード ビジュアルと 作業項目数 メジャーを最初のカードに追加し、 作業項目 ID プロパティを 2 番目のカードに追加することによって作成されます。
既定の集計を使用して正しいカウントを取得するには、フィルターを適用 現在 は "True" です。 既定の集計にフィルターを適用するこのパターンは、この記事で提供されている多くの例の基礎となります。
状態の並べ替え順序の追加
既定では、Power BI では、視覚エフェクトでアルファベット順に並べ替えられた状態が表示されます。 状態の時間を視覚化し、進行中の後に提案が表示される場合は、誤解を招く可能性があります。 次の手順は、この問題の解決に役立ちます。
[状態カテゴリ] フィールドが [分析] ビューに含まれていることを確認します。 このフィールドは、すべての既定の共有ビューに含まれます。
データ テーブルを選択し、リボンの [ テーブル ツール ] タブの [ 計算 ] セクションから [ 新しい列] を選択します。
既定のテキストを次のコードに置き換え、チェックマークを
選択します。
State Sort Order = SWITCH ( 'View Name'[State Category], "Proposed", 1, "InProgress", 2, "Resolved", 3, 4 )
次の例を参照してください。
Note
State Category が提供するより細かい粒度が必要な場合は、定義の変更が必要になる場合があります。 状態カテゴリ は、 状態 のカスタマイズに関係なく、すべての作業項目の種類に対して正しい並べ替えを提供します。
[データ] ビューを開き、[状態] 列を選択します。
[ 列ツール ] タブで、[ 列で並べ替え ] を選択し、[ 状態の並べ替え順序 ] フィールドを選択します。
前の日付の追加
状態の時間を計算するための次の手順では、データセット内のデータ行ごとに前の間隔 (日、週、月) をマッピングする必要があります。 これは、計算列を使用した単純な計算です。 通常、この列は次のように定義します。
Date Previous =
PREVIOUSDAY ( 'View Name'[Date] )
ただし、この方法には主に次の 2 つの問題があります。
- これは、毎日の期間にのみ機能します。
- データのギャップは処理されません。 たとえば、作業項目がプロジェクト間で移動された場合などです。
これらの問題を解決するには、計算列で [日付 ] フィールドをスキャンして前日を検索する必要があります。
[前の日付] 集計列を追加するには、[テーブル ツール] タブで [新しい列] を選択し、既定のテキストを次のコードに置き換えて、チェックマークを選択します。
Date Previous =
CALCULATE (
MAX ( 'View Name'[Date] ),
ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
'View Name'[Date] < EARLIER ( 'View Name'[Date] )
)
Date Previous 計算列では、MAX
この記事の後半で詳しく説明する 3 つの DAX 関数 、ALLEXCEPT
、、および EARLIER
を使用します。 列は計算されるため、テーブル内のすべての行に対して実行され、実行されるたびにその特定の行のコンテキストが含まれます。
ヒント
[日付] フィールドと [前の日付] フィールドのコンテキスト メニューで、[日付階層] ではなく [日付] を選択して、これらのフィールドの 1 つの日付を表示します。
日付の差分を日単位で追加する
Date Previous は、各行の前の日付と現在の日付の差を計算します。 日付の差分 (日数) を使用して、これらの各期間の日数を計算します。 毎日のスナップショット内のほとんどの行の場合、値は 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 関数を使用します。
[Add Is Last Day in State]\(追加は状態の最後の日\)
この次の手順では、特定の作業項目が状態にあった最後の日を特定の行が表しているかどうかを計算します。 これは、次のセクションで追加する Power BI の既定の集計をサポートしています。ここでは、[ State Time in Days]\(日数単位の状態時間 \) 列を追加します。
[ モデリング ] タブで [ 新しい列 ] を選択し、既定のテキストを次のコードに置き換えて、チェックマークを 選択します。
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] )
))
状態時刻を日単位で追加する
作業項目が特定の状態で費やされた時間は、各作業項目の 日付の差分 (日数) を合計することで計算できるようになりました。 この計算には、状態を複数回切り替えた場合でも、特定の状態で費やされたすべての時間が含まれます。 日付 または最新 の情報を使用して、各行を傾向として評価するには、 Is Last Day In State を使用します。
重要
テーブルに Date Diff in Days と Is Last Day in State 計算列を追加している必要があります。
[ モデリング ] タブで [ 新しい列 ] を選択し、既定のテキストを次のコードに置き換えて、チェックマークを 選択します。
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
状態時間 (日数) に基づいて積み上げ縦棒グラフを作成する
[State Time in Days]\(日数単位の状態時間\) 列を示すために、次の積み上げ縦棒グラフが作成されます。 最初のグラフは、時間の経過に伴う各状態の作業項目の数を示しています。
2 番目のグラフは、アクティブな作業項目が特定の状態にある平均日数の傾向を示しています。
状態時刻を日単位で追加する - 最新 (状態の最終日)
テーブル内の各作業項目の状態の時間を評価する場合、または エリア パスなどのフィールドでフィルター処理する場合は、集計で [State Time in Days]\(日数単位の状態時間 \) 列を使用しないでください。 集計では、作業項目が状態であった毎日の値が使用されます。 たとえば、月曜日に作業項目が In Progress で、木曜日に Completed に移動した場合、状態の時間は 3 日ですが、 State Time in Days 列の合計は 6 日 ( 1+2+3
) になります。これは正しくありません。
この問題を解決するには、 State Time in Days を使用し、フィルター Is Last Day In State は "True" に設定します。 傾向に必要なすべての履歴データが排除され、各状態の最新の値だけに焦点が当てられます。
状態時刻を日数で追加する - 進行中
前の例では、特定の作業項目の State Time in Days は、作業項目がその特定の状態の場合にのみカウントされます。 目標が、特定の作業項目の状態が平均に対して継続的にカウントされる場合は、計算を変更する必要があります。 たとえば、"進行中" 状態を追跡する場合は、[ 状態時間 (日数 ) - 進行中 ] 計算列を追加します。
[ モデリング ] タブで [ 新しい列 ] を選択し、既定のテキストを次のコードに置き換えて、チェックマークを 選択します。
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
Note
プロジェクトで使用されるワークフローの状態に基づいて定義を変更する必要がある場合があります。 たとえば、この記事の例で使用されているプロジェクトでは、"進行中" ワークフロー状態が使用されますが、アジャイル、スクラム、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
Note
プロジェクトで使用されるワークフローの状態に基づいて定義を変更する必要がある場合があります。 たとえば、プロジェクトで 'Committed' または 'Proposed' の代わりに 'Active' が使用されている場合です。
左側のグラフには平均が組み合わされ、右側には個々の状態が表示されます。
複数の状態の状態時間 (日数- 最新) を取得する
傾向を作成するときは、 状態時間 (日数- 最新 の計算列) を使用します。 状態のフィルターを使用すると、 State Time in Days 列と Is Last Day in State は、一連の状態で費やされた作業項目または作業項目のグループの合計時間を取得する簡単な方法を提供します。
前の状態の追加
[前の日付] 計算列を使用して、各作業項目の以前の状態などの過去の値を検索することもできます。
重要
テーブルに 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]
現在の行と一致する作業項目 ID を持つ行のみを考慮するように指定します。
また、最後のパラメーター である は、 'View Name'[Date], 'View Name'[Date Previous]
返される行の日付に、現在の行の [前の日付] と一致する [日付] が必要であることを指定します。 スナップショットでは、この条件を満たすことができる行は 1 つだけです。
変更された状態の追加
[前の状態] 列を使用すると、状態遷移が発生した各作業項目の行にフラグを設定できます。 ステージ変更計算列には、次の 2 つの特別な考慮事項があります。
- *State Previous の空白値。作業項目の Created Date に設定します。
- 作業項目の作成は状態遷移と見なされます
重要
テーブルに 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 Previous と State Changed の計算列を使用すると、特定の作業項目の状態フローを示す列を作成できます。 この記事では、この列の作成は省略可能です。
重要
テーブルに State Previous および State Changed 計算列を追加している必要があります。
[ モデリング ] タブで [ 新しい列 ] を選択し、既定のテキストを次のコードに置き換えて、チェックマークを 選択します。
State Flow =
IF([State Changed], [State Previous], [State]) & " => " & [State]
状態変更数の追加
より複雑なメジャーに進むには、特定の作業項目のデータ行を比較するために、状態変更の合計数を表す必要があります。 状態変更数の計算列を追加することで、表現を取得します。
重要
State Changed 集計列をテーブルに追加している必要があります。
[ モデリング ] タブで [ 新しい列] を選択し、既定のテキストを次のコードに置き換え、チェックマークを 選択します。
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
状態変更数の追加 - 最後に提案された日時と状態の再起動時間 (日数)
状態の再起動時間 (日数 はかなり複雑な計算です。 最初の手順は、作業項目が提案された状態だった最後の時刻を見つけることです。 [State Change Count - Last Proposed] 計算列を追加します。
Note
プロジェクトで使用されるワークフローの状態に基づいて、次の定義を変更することが必要になる場合があります。 たとえば、プロジェクトで 'Proposed' の代わりに 'New' を使用している場合です。
Modeling タブで、[新しい列選択し既定のテキストを次のコードに置き換え、のチェックマークを選択します。
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 を使用して個々の作業項目のリワークを調べることができます。
状態のリワーク時間を日単位で追加する
State Restart Time in Days と同様に、State のリワーク時間 (日数)は、作業項目が最初に完了状態カテゴリに入った時刻を検索します。 その後、作業項目が完了以外の状態で毎日費やされ、再作業としてカウントされます。
[State Change Count - First Completed] 列を作成します。 この列は、作業項目が他の状態から完了状態に遷移する回数を追跡します。
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]) ) )
[ モデリング ] タブで [ 新しい列] を選択し、既定のテキストを次のコードに置き換え、チェックマークを
選択します。
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 )
Note
プロジェクトで使用されているワークフローの状態に基づいて、前の定義を変更することが必要になる場合があります。 たとえば、プロジェクトで Done Closed の代わりに使用する場合などです。
DAX 関数
このセクションでは、この記事で追加された計算列とメジャーの作成に使用される DAX 関数に関する追加情報を提供します。 「DAX、タイム インテリジェンス関数」も参照してください。
機能 | 説明 |
---|---|
ALLEXCEPT |
指定した列に適用されたフィルターを除き、テーブル内のすべてのコンテキスト フィルターを削除します。 基本的に、 ALLEXCEPT ('View Name'', 'View Name'[Work Item Id]) テーブル内の行を、現在の行と同じ作業項目 ID を共有する行のみに減らします。 |
CALCULATE |
この関数は、ほぼすべての例の基礎です。 基本構造は、式の後に、式に適用される一連のフィルターです。 |
COUNTROWS |
この関数 は、 COUNTROWS ( 'View Name' ) フィルターが適用された後に残っている行の数をカウントするだけです。 |
DATEDIFF |
2 つの日付の間にある間隔の境界数を返します。 DATEDIFF は Date から Date Previous を減算して、それらの間の日数を決定します。 |
EARLIER |
指定された列の外側の評価パスにある、指定された列の現在の値を返します。 たとえば、 'View Name'[Date] < EARLIER ( 'View Name'[Date] ) では、 EARLIER 関数を使用して参照されている現在の行の日付より前に発生した行のみにデータ セットをさらに減らします。 EARLIER 以前の日付を参照していません。計算列の行コンテキストを具体的に定義します。 |
ISBLANK |
セルの内容が空白であるかどうかをチェックし、TRUE または FALSE を返します。 ISBLANK は現在の行を評価して 、Date Previous に値があるかどうかを判断します。 そうでない場合、If ステートメントは Date Diff in Days を 1 に設定します。 |
LASTDATE |
たとえば、 式にフィルターを LASTDATE 適用して、 LASTDATE ( 'View Name'[Date] ) テーブル内のすべての行の最新の日付を検索し、同じ日付を共有していない行を削除します。 Analytics ビューによって生成されたスナップショット テーブルを使用すると、このフィルターは選択した期間の最終日を効果的に選択します。 |
LOOKUPVALUE |
search_columnName および search_value で指定されたすべての条件を満たす、行の result_columnName の値を返します。 |
MAX |
列内の、または 2 つのスカラー式間で最大の数値を返します。 MAX ( 'View Name'[Date] ) を適用して、すべてのフィルターが適用された後の最新の日付を決定します。 |