Power BI 勉強会 というのをここしばらく催しているのだが、出席されている方の動向というか題材として整理しておこうかなと。各回で 初参加 や リピートで参加されているのがどれくらいとかメジャーを説明するのに取り上げてみる。
- メジャー でよく使うDAX 関数
- メジャーで使うとき便利な DAX 関数
DAX 関数にはこの2種類があって、
- それぞれの使い方
- 組合せたパターン
を覚えてしまうことがよいかな。
サンプルのデータ
特に説明する内容ではありませんが、"イベント" の一覧があってそのイベントに誰が参加したかの結果のみ。
Sample.pbix
各自学習用にのみご利用いただいて構いませんが、各自の責任においてご利用ください。
以下、DAX クエリ で記述しているので、DAX Studio を使うとよいです。
- DAX Studio とは
- DAX Query を記述して メジャーを試す
メジャーを定義してみる
ファクトテーブルに該当する '参加'テーブル の行数をカウントする。
参加数 = COUNTROWS( '参加' )
COUNTROWS Function (DAX) - Statistical Functions
テーブル もしくは テーブル式 で評価される テーブル の行数を返す
シンプルなカウント方法なのだけど、データの持ちようによっては "延べ" ということになりますね。一意のユーザIDのカウントであれば、
参加数 = COUNTROWS ( VALUES ( '参加'[ユーザID] ) )
参加数 = DISTINCTCOUNT ( '参加'[ユーザID] )
初参加とそれ以外を集計
ひとつめのパターン
DEFINE
MEASURE '参加'[参加数] =
COUNTROWS ( '参加' )
MEASURE '参加'[参加(2+)] =
COUNTROWS (
CALCULATETABLE (
VALUES ( '参加'[ユーザID] ), -- A
VALUES ( '参加'[ユーザID] ), -- B
FILTER ( ALL ( 'イベント' ), 'イベント'[開催日] < MIN ( 'イベント'[開催日] ) ) -- C
)
)
MEASURE '参加'[参加(初)] = [参加数] - [参加(2+)]
EVALUATE
SUMMARIZE (
'イベント',
'イベント'[タイトル],
"参加数", [参加数],
"参加(初)", [参加(初)],
"参加(2+)", [参加(2+)]
)
A と B の VALUES が同じなので ちょっと？？と思うけど、フィルターがどのように影響しているかを整理するとよいかも。
VALUES Function (DAX) - Filter Functions
指定した列に含まれる一意の値をひとつの列を持つテーブルとして返す。
CALCULATETABLE Function (DAX) - Filter Functions
適用されるフィルターを変更して、テーブル もしくは テーブル式を評価する。CALCULATE と同じ役割なのだけど、テーブル / テーブル式に使用できないので CALCULATETABLE を使う。
該当のメジャーは、
- フィルター B と フィルター C の内容で A を評価
- 評価結果 A (テーブル) の行数
を返すのだけど、もう少し詳細に。
EVALUATE で返される結果の各行が集計されるとき、
C - 結果セット各行の対応するイベントより前に開催されたイベント
B - 結果セット各行に対応するイベントに関連する ユーザID(一意) の一覧
A - B と C の条件を満たす ユーザID(一意) の一覧
結果として各行に対応するイベントより前に参加した ユーザID(一意)の一覧 になるので、その行数がそれまでにイベントに参加したユーザ数ということになる。
別のパターン
DEFINE
MEASURE '参加'[参加数] =
COUNTROWS ( '参加' )
MEASURE '参加'[参加(初)] =
VAR CurrentEventUsers =
VALUES ( '参加'[ユーザID] ) -- A
VAR UptoPrevEventsUsers =
CALCULATETABLE (
VALUES ( '参加'[ユーザID] ),
FILTER ( ALL ( 'イベント' ), 'イベント'[開催日] < MIN ( 'イベント'[開催日] ) )
) -- B
RETURN
COUNTROWS (
EXCEPT ( CurrentEventUsers, UptoPrevEventsUsers ) -- C
)
MEASURE '参加'[参加(2+)] = [参加数] - [参加(初)]
EVALUATE
SUMMARIZE (
'イベント',
'イベント'[タイトル],
"参加数", [参加数],
"参加(初)", [参加(初)],
"参加(2+)", [参加(2+)]
)
EXCEPT Function (DAX) - Other Functions
テーブルの間に重複する行を除外したテーブルを返す。引数の順番は意味を持つ。
A - 結果セット各行に対応するイベントに関連するユーザID(一意)の一覧
B - 結果セット各行の対応するイベントより前に開催されたイベントに関連するユーザID(一意)の一覧
C - 重複したユーザIDを除外した結果(テーブル)
EXCEPT ではなく INTERSECT Function (DAX) - Other Functions を使用すれば、重複するユーザIDのみのテーブルが返るので、リピート参加するユーザID数になる。
参加回数をカウントするパターン
DEFINE
MEASURE '参加'[参加数] =
COUNTROWS ( '参加' )
MEASURE '参加'[参加(初)] =
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( '参加'[ユーザID] ), -- A
"Count", CALCULATE (
COUNTROWS ( '参加' ),
FILTER ( ALL ( 'イベント' ), 'イベント'[開催日] < MIN ( 'イベント'[開催日] ) )
) -- B
),
ISBLANK ( [Count] ) -- C
)
)
MEASURE '参加'[参加(2+)] = [参加数] - [参加(初)]
EVALUATE
SUMMARIZE (
'イベント',
'イベント'[タイトル],
"参加数", [参加数],
"参加(初)", [参加(初)],
"参加(2+)", [参加(2+)]
)
A - 結果セット各行に対応するイベントに関連するユーザID(一意)の一覧
B - 結果セット各行の対応するイベントより前に開催されたイベントに参加した回数を A に ユーザIDごとに列として追加
C - 追加した列を条件としフィルタを適用
0 行のテーブルもしくはテーブル式であるとき、COUNTROWS 関数 は BLANK を 返すので FILTER Function (DAX) - Filter Functions のフィルターには ISBLANK Function (DAX) - Information Functions を使っている。ここには 真偽 を返せばよいので、 [Count] > 1 とすれば これまでにに2回以上(今回3回目以上)参加したユーザIDを得ることができる。