Power BIで重複する時間帯をチェックする

突然ですが、以下のように社員の作業時間を管理しているデータがあったとします。

ここで、担当者別に時間帯が重複している行を特定したいと思ったとき、Power BIではどのような実装を行えば良いでしょうか？

実装方法

Power BIの開発全般に言えることですが、このようなデータチェックの処理を実装する場合、Power Query側でやるのかDAX側を使うのかをまず考える必要があります。
Power Query側で重複チェックを行う場合は、テーブルを入れ子にするとか、同じクエリを2つ作ってマージするとかという方法が考えられますが、今回はよりシンプルな実装を目指しDAX側での対応を考えてみたいと思います。

今回の例ではそれほど複雑な処理は必要なく、以下の条件に合致するレコードがないかをアンド条件で探せばOKです。

• そのレコードの担当者=それ以外のレコードの担当者
• そのレコードの開始日時<それ以外のレコードの終了日時
• そのレコードの終了日時>それ以外のレコードの開始日時

具体的には、上記の条件でレコード数をカウントする列を追加して、結果が1より大きいものを抽出すればよい、ということになります。

まずはPower BIでExcelファイルを読み込み、テーブルビューの画面から・・

列を追加して数式を書いていきます。

・・が、実際にDAXの式を書いていくと、ふと、自分のレコード（行）ってどうやって表せばいいんやっけ？という問題にぶつかりがちです。というのも、DAXではExcelとは違いセル指定ではなく列指定で計算式を記述していくことになるためです。

なので、普通に書いていくと下記のように[列A]=[列A]みたいな条件になってしまってうまくカウントすることができません。

重複時間帯チェック_NG = 
COUNTROWS(
    FILTER(factActivityLedger,
        [担当者コード]=[担当者コード]
        &&[開始日時]<[終了日時]
        &&[終了日時]>[開始日時])
)

どうすればよいか

DAXを使って自分自身のレコードを取得するにはvar（変数）を利用する方法があります。
以下のようにいったんvarに値を格納してから比較をすることで、レコード単位でフィルター条件式の中身を評価することができます。

見た感じ複雑に見えますが、var（変数）を宣言して値を格納した後に、return（実際に返す値）の中でそれを使う感じです。

重複時間帯チェック_OK = 

var CurrentRowEmployee=[担当者コード]
var CurrentRowStartDatetime=[開始日時]
var CurrentRowEndDatetime=[終了日時]

RETURN
COUNTROWS(
    FILTER(factActivityLedger,
        CurrentRowEmployee=[担当者コード]
        &&CurrentRowStartDatetime<[終了日時]
        &&CurrentRowEndDatetime>[開始日時])
)

ここで、上記の10-12行目の条件式の左辺には自分自身のレコードに限定された値が代入されることにより正しく重複する時間帯をカウントできる、というのがポイントです。

あとはレポート作成画面でフィルタリングしてあげれば作業時間帯が被る活動だけを表示することができますね。

※実際には人はテレビを見ながら寝ることがあるので、1/1の9:45～10:00は矛盾していないのかもしれません。が、1/2の13:00-13:05は寝ながら帳簿をつけているので明らかにNGですね・・。

まとめ

ということで、Power BIの開発において知っていれば簡単に実装できるけど知らなければとことんハマるというものの定番の1つ、「自分自身のレコードを参照する」について解説してきました。

別の実装方法としてはEARLIER関数を使うという方法もありますが、使う関数は少なければ少ないほどよいので、varを使う方法がおすすめです。

ちなみに、上記のような動きになるのは、varを式内から切り出すことで行コンテキスト内で評価された結果をフィルターコンテキスト内に持ち込む動きになるから、ということなのですが、まあ難しいことは考えずにどんどんトライしていきましょう。