【Power BI】過去の自分を超えろ！実績データからセットの件数をカウント2021バージョン

※2021年6月26日追記

本日、DAX Boot Camp#11に久々に参加させて頂き、こちらの件について発表させて頂いたのですが、
DAXというよりモデリングの問題というダメ出しを各方面から頂いたので、頑張ってモデリングから考え直し中です。

##ちょっぴり前進できたような気がしていた去年の自分

去年の夏、DAX Boot Camp#1に参加させて頂き、こんな記事を書きました。

DAX Boot Camp#1に参加してまた少し前進できた件

アイス屋さんの販売実績データから、5商品のセットが何セット販売された実績をカウントするというもの。

以前だったら裏（Power Query）で対応していたものが、DAXでできるようになったぜ！ﾄﾞﾔ！！という記事です。

##過去の自分に教えてもらう・・・？

さて先日、営業さんからこんな依頼がきたんですよね。

『以前集計してもらったセットの件ですが、セットを増やしましたので、そちらの集計もお願いできますでしょうか。セットの内容は添付いたします。』

添付ファイルを開くと・・・

な、なんと、セットが50種類に！！！
なんで？なんでそんなに作った？？
しかも、セットによって商品数がバラバラだよ！！

泣きながら、「そう言えば、前に集計した時ってどうやったんだっけ？」って自分のQiita記事を読み返しました。
・・・・ﾊｯ
何故あれだけﾄﾞﾔｧな感じで書いた内容のことが身についてないんじゃ・・・？過去の自分に負けとるやないけ・・・

でも、背に腹は代えられん・・・・と自分のQiita記事を見ながら集計を進めていったのですが、
アレ、このやり方だとマスタをそれぞれ別に作らなくちゃならないし、いちいちRelatedで列追加ってメンドウだし、しかもなんでIDごとのテスト数を実績データに列追加してるんじゃ・・・？
と要修正ポイントが。

やっぱりほんとにちょっとづつだけと、成長してるのね、あたい！！

##実績データからセットの件数をカウント2021バージョン

去年のわたしのQiita記事を参考にしてくださった方がいたらごめんなさいm(__)m（いないか・・・）
セットの数が50種類になっても（なんとか）耐えられる方法を考えましたので、改めてこちらをご参照ください。

###【やりたいこと】
●200ほどある商品を組み合わせてセット販売を行ったが、どれくらいセットが売れているのか集計したい。（セットの種類は50）

●ただし、実績データ（販売履歴）ではセットではなく、単品でのデータになっている。

●実績データの、販売日・営業所コード・店舗コード・顧客番号を使用して、顧客が同時に購入したアイテムを抽出可能。

実績データはこんな感じ。
セットのマスタはこんな感じで用意します。

セットに含まれる商品名の行に1を入れます。
こうすることによって、セットのマスタを1つにすることができます。

####①裏（Power Query）で実績データにセットのフラグをマージする。
去年のあたいは、Related関数を使って、フラグ列を追加していたのですが、そんなこと50回もやってられるかい！！というわけで、裏でマージして、列を展開。

以前、これもQiita記事で「裏でやるか、表でやるか？」というのを書いたんですが、その一つの解としては、「効率がいい方でやる！」ですね。
####② SUMMRIZE関数を使用して、購入商品数テーブルを作成
IDでグループ化して、ID数をカウントすると購入商品数が集計できる。

商品数 = ADDCOLUMNS(SUMMARIZE('Data',Data[ID]),"商品数",CALCULATE(COUNTROWS(Data)))

まぁこれも裏でやってもいいかなとも思います。
####③IDごとにセットに含まれる商品を何個買っているかカウントするテーブルを作成
さて、ここが今回のキモでございます。
こんな感じで実績データをSUMMRIZEして、IDごとに各セットに含まれる商品を何商品購入したかを集計するテーブルを作成します。

SetT = 
ADDCOLUMNS(
    SUMMARIZE('Data','Data'[ID]),
    "S1",CALCULATE(COUNTROWS('Data'),'Data'[S1]=1),
    "S2",CALCULATE(COUNTROWS('Data'),'Data'[S2]=1),
    "S3",CALCULATE(COUNTROWS('Data'),'Data'[S3]=1),
    "S4",CALCULATE(COUNTROWS('Data'),'Data'[S4]=1),
    "S5",CALCULATE(COUNTROWS('Data'),'Data'[S5]=1),

こんな感じで延々50行分書きます・・・
####④各テーブルをリレーションします。

去年は実績データと商品数テーブルをリレーションしましたが、2021バージョンはSetテーブルと商品テーブル、Setテーブルと実績データをリレーションします。
####⑤セット商品を全て購入しているIDをカウントするメジャーを書きます。
例えば、S1セットは4商品のセットなので、S1セットを購入していると考えられるお客は、SetテーブルのS1=4で、商品数=4のIDということになります。
なので、この条件をカウントするメジャーを書けばいいわけです。

S1数 = CALCULATE(COUNTROWS('SetT'),'SetT'[S1]=4,'商品数'[商品数]=4)

これも、延々50個書けたら、完了です・・・・。

##過去の自分を超えろ！ひとつのやり方で満足しない！
今回、こんな感じでできましたが、もしかしたらもっといい方法が見つかるかもしれません。
ひとつのやり方で満足せずに、他にもやり方ないかな？っていつも考えるくせが必要ですね。