この記事で分かること
マスターテーブルのキー列(ID番号など)の中で、データが重複している行を見つける方法
COUNTROWS関数とFILTER関数の実践的な使い方
DAX関数で解決する課題
今回の課題は下記のようなケースで、データが重複している行を抽出することです。
日毎の売上個数に、商品マスタから取得した商品名や単価を組み合わせて、商品ごとの売上高を計算することを想定します。商品マスタと売上個数テーブルは項目「ID」をキー列としてリレーションシップを形成します。
商品マスタテーブル
売上個数テーブル
PowerBIでのリレーションシップ作成中に下の画面赤枠部分のような「このリレーションシップのカーディナリティは多対多・・・」というエラーに遭遇することはないでしょうか？
このエラーはマスターテーブルのキー列の中で、値が重複している行が存在していることを意味します。よって重複行を見つけるのがエラー解消の第一歩です。
しかし数行のテーブルならともかく、数百行とか数千行のテーブルなら、重複行の発見は可能でしょうか？キー列を昇順に並び替えて重複行を一行ずつ目視チェック？・・・それは時間がかかりますね。
実は、PowerBIならDAX関数式を一つ追加すれば簡単に解決できるんです。今回はそのDAX関数式と計算ロジック、PowerBI上での実際の解決方法をご説明します。
DAX関数式と解決策
DAX関数式
データ重複回数＝
var currentRow=[ID]
Return
COUNTROWS(
FILTER(
’商品マスタ’,
[ID]=currentRow
)
)
解決策
商品マスタに新しい列を作り、上記のDAX関数式を入力します。
下図のように、テーブルビューで商品マスタテーブルを選択し、「データ重複回数」が2以上 でフィルタリングします。そしてキー列(列名：ID)を昇順に並び替えれば重複した値が見つかります。
DAX関数式の説明
全体の処理
この関数式はマスターテーブルのキー列の各行について、重複した値が何個あるか数えて戻り値として返します。
1行目の戻り値を例に取ります。重複がなければ、1行目のDAX関数式の戻り値は1です。重複があれば、例えば1行目と15行目と41行目の値が重複していれば、戻り値は3です。
同様に2行目の戻り値は、2行目以外と重複した値の個数を返します。同様に最終行まで戻り値を計算します。
各関数の処理
FILTER関数での処理
1行目を例に取ります。1行目のキー列の値を変数currentRowに格納して、currentRowと重複した行のみを抽出したテーブルを戻り値として返します。
重複がなければ1行目のキー列のみを抽出したテーブルを返します。
同様に2行目から最終行までテーブルを返します。
COUNTROWS関数での処理
FILTER関数が生成したテーブルの行数をカウントします。戻り値は各行の重複回数です。
まとめ
以上、テーブル内の指定した列に関して、重複データを抽出する方法を説明しました。
マスターテーブルのキー列の中で、どのキー値が重複しているか知りたい時に便利なテクニックです。
式の計算ロジックは少々難解ですが、各行の値を変数に格納して、その行と重複した行だけを抽出する、というのがポイントです。
少しでもPowerBI上達に役立てれば幸いです。（終わり）
コメント