こちらのコーナーでは、DAX関数＊をうまく使うためのコツを解説します
＊エクセルのワークシートでは関数に相当するもの
フィルター評価と行評価
フィルター評価
Power BIのレポート画面では常にフィルター評価の影響を受けます
この点は常に意識しておく必要があります
例えば、次のようなデータがあったとします
こちらのデータをマトリックスで表示すると次のようになります
こちらのマトリックスの行にカラーを入れてみます
こちらは行単位で青、赤、緑のフィルターが適用されています
上の画像では、スライサーのフィルターが適用されています
このように、レポート画面ではフィルターの影響を常に受けるわけですが、DAXも同様です
但し、CALCULATEをDAX式で活用するとルールが変わります
試しに、青色の金額を抽出するCALCULATE関数でメジャーを書いてみます
Color_BLUE = CALCULATE(SUM(myTable[金額]),’myTable'[カラー]=”青”)
スライサーのフィルターを解除した上で、上記のメジャーをマトリックスに配置してみます
マトリックスの行単位での色別のフィルターは解除され、全て同じ値/6,600になっています
今度は、スライサーのフィルターを適用してみます
DAX外のフィルターはきちんと適用されているのが分かります
今度はメジャーにKEEPFILTERS関数を適用してみます
今度は、行単位での色別のフィルターが適用されています
以上、レポート画面では常にフィルターの影響を受ける点とCALCULATE関数を使うとルールが変わる点は常に意識しておきましょう！
行評価
フィルター評価と同じくらい重要で、フィルター評価より難解なのが行評価です
こちらは、明確にフィルター評価と区別して理解しましょう
例えば、次のようなデータがあるとします
こちらのデータから、行毎に値１と値２を乗算する列を作成してみます
次に値１ｘ値２の列を合計する列を作成します
では上の画像と同じ「５５」を算出するメジャーを、新しく作成した列をつかわないまま作成するにはどうしたらいいでしょう？
次のように式を書くと、突拍子もない数字になります
これはSUM関数が列自体を計算対象にしているからです
では、ここでSUMX関数を使用してみましょう！
expressionの箇所が「行評価」になっています
SUMX関数を使うと、テーブル「myNum」の1行1行を計算して合計するので正しい数字が出ています
これが行評価となります
行評価では、新しい列の作成と同じように1行1行を対象とするので、フィルター評価とは明確に区別する必要があります
ちなみにCALCULATE関数を使用すると、フィルター評価と同じようにルールが変わります
今回は、隣の列全体を合計するのではなく、1行1行の合計値を算出しています
このCALCULATE関数を使うとルールが変わる点は常に頭に入れておきましょう！
VARとRETURN～中学数学の要領でDAX使用～
DAXはVARとRETURNを使うと更に有効の活用できます
というより、VARとRETURNを使わないとかなりDAXを使う意義が薄れます
VARは変数/VARIABLEの略です
このVARを箱として活用します
エクセルのワークシートでも同じことができます
B2セルにmyValueという名前をつけました
この名前がVARに相当します
そしてD２セルで下の画像のようにmyValueを使ってみます
このD2セルの３がRETURNになります
ちなみに、B2セルの値のmyValueを２から３に変えるとD4セルのリターンは４になります
では、上記と同じ内容をPower BI内でも行ってみます
上の画像では、myValueという箱に２を入れ、更に１を加えてリターンしています
では、myValueを３にすると４がリターンします
実際には、このVARを多数組み合わせて使う形になります
ALLとALLSELECTEDとの使い分け～構成比の罠～
ALLとALLSELECTEDの違いがDAXを使用したばかりだとよくわからないと思います
そもそも、ALLって何のためにあるの？という方も中にはいらっしゃると思います
ALLはスライサーなどでフィルター処理が行われた場合のためにあります
上のGIF画像では、分子がフィルターされた場合にも、分母の全体売上はALL関数により変わらないようになっています
ところが、分母の内容自体がフィルターされる場合が厄介です
上の画像では、構成比の分母は各都道府県の合計になります
仮に３つの都道府県にフィルターした場合には次のようになります
構成比_ALLの場合には、分母が固定ですから、小計が100%を割ります
構成比_ALL = DIVIDE(SUM(‘売上データ3′[売上金額]),CALCULATE(SUM(‘売上データ3′[売上金額]),ALL(‘売上データ3’)))
構成比_ALLSELECTEDの場合には、フィルターと連動して分母が動きます
構成比_ALLSELECTED = DIVIDE(SUM(‘売上データ3′[売上金額]),CALCULATE(SUM(‘売上データ3′[売上金額]),ALLSELECTED(‘売上データ3’)))
つまり、分母が何か？スライサーでフィルターする内容は何か？によってALLとALLSELECTEDを使い分ける必要があります
SUMX関数による高度な集計
エクセルのワークシート関数でもSUM系の関数は多数あります
SUM、SUMIF、SUMIFS、SUMPRODUCT・・・
DAXのSUMX関数は、ワークシート関数のSUMIFS関数とSUMPRODUCT関数を組み合わせた高性能なDAX関数です
次の表を見てください
値１の列と値２の列をそれぞれ掛け合わせ、E8セルで合計しています
E10セルにはSUMPRODUCT関数が入っており、列１と列２をそれぞれ乗じつつ、各行の結果を合計しています
H3セルにはSUMIFS関数が入っており、G3セルの条件に応じて、列１と列２を乗じた値の合計を計算しています
但し、Eの列で算出した値を参照しています
SUMPRODUCT関数のように、単独では計算できません
DAXのSUMX関数では、このSUMPRODUCT関数とSUMIFS関数の組み合わせを一発で計算できます
SUMX関数の文法は次のようになります
SUMX(テーブル,式）
試しに、前述のSUMPRODUCT関数と同じことをしてみます
元データは前と同じデータ（テーブル名：myTable）になります
下の画像では、myTableを第一引数のテーブルとして指定し、第二引数で式を指定しています
mySUMX = SUMX(‘myTable’,’myTable'[値1]*’myTable'[値2])
今度は、SUMIFS関数と同じように列１の値が＜３とします
この場合は、第一引数のテーブルにFILTER関数を使います
mySUMX_FILTER = SUMX(FILTER(‘myTable’,’myTable'[値1]<3),’myTable'[値1]*’myTable'[値2])
これで、SUMIFS関数とSUMPRODUCT関数との組み合わせを計算できています
嬉しいのはSUMX関数ではSUMPRODUCT関数と違い、式を自由に指定できる点です
DAXの可能性をまさに感じさせる関数だと思います
SUMMRIZE関数による一時テーブルの作成
DAX関数内で参照用の一時テーブルを作成するのにとても便利です
文法は次の通りとなります
SUMMRIZE(テーブル,集計する切り口にする列,新列名1,集計式1,新列名2,集計式2・・・)
早速、使用例を見てみましょう
元のデータは次の通りです
これが上の画像のテーブルから都道府県別の売上合計、平均を算出するSUMMRIZE関数の事例１です
mySummarize = SUMMARIZE(‘売上データ3’,’売上データ3′[都道府県],”Revenue”,SUM(‘売上データ3′[売上金額]),”AVERAGE”,AVERAGE(‘売上データ3′[売上金額]))
事例２として集計の切り口を都道府県のみから、商品も加えてみましょう
mySummarize+Product = SUMMARIZE(‘売上データ3’,’売上データ3′[都道府県],’売上データ3′[商品],”Revenue”,SUM(‘売上データ3′[売上金額]),”AVERAGE”,AVERAGE(‘売上データ3′[売上金額]))
ピボットテーブルと同じような形ですよね
HASONEVALUE～値が複数か否か～
こちらのDAXは、値が複数あるケースとそうでないケースで表示を分けるのに便利です
値が複数ある場合は、FALSE、そうでない場合はTRUEを返します
ただ、上記の説明だと使用シーンが明確に浮かばないとおもうので実際の例を見てみましょう
上の画像にあるようなメジャーを作り、カレンダーテーブルの内容を行に仕込んだマトリックスの中にいれてみます
すると、MONTHの箇所は一つしか値がないのでTRUEになり、YEARの箇所はMONTHが複数あるのでFALSEになります
次にもう一つメジャーを作成します
IF式を使い、HASONEVALUEがTRUEであれば、金額を表示するようにします
YEARとMONTHで表示を切り替えられるようになりました
ISBLANKとISEMPTY～空白？空欄～
ISBLANKとISEMPTY、似てるようで明確に違いがあります
具体的には何が違うのでしょう？
ISBLANKは対象が列、ISEMPTYはテーブルが対象になります
ISBLANK
下の画像では「ISBLANK1」という新しい列を追加しています
空欄の行では「TRUE」を返しています
次の画像ではIF式と組み合わせて空欄を０に置き換えています
ISEMPTY
ISEMPTY関数を使い、次のようなメジャーを作成してみます
上の画像内の「’EMPTY’」は次の画像のテーブルです
このメジャーの内容をカードで出力してみます
こちらはTrueで出力されます
今度はISEMPTYの中身のテーブルを変えてみます
’テーブル’は空でないのでFalseが返されます
REMOVEFILTERS関数～フィルターの影響を避ける～
思わぬところでフィルターの落とし穴にはまったことはないですか？
以下のグラフでは折れ線グラフが100%のまま水平になっています
これは元のデータにはABCだけでなく、EやFもあるのです
ただ、フィルターがかかっています
このフィルターがかかっている状態にて、折れ線グラフで使用するメジャーは次のような数式になっているのです
分子のメジャーは商品ABCの合計です
分母は全体の合計です
ただ前述のようにフィルターはABCにかかっています
ですので分母は常に商品ABCです
だから100%のままです
この場合にREMOVEFILTERS関数を使用します
A&B&C %_2 = DIVIDE(‘DATA'[Sales_A&B&C],CALCULATE(SUM(DATA[値]),REMOVEFILTERS(‘DATA'[商品])))
このREMOVEFILTERS関数により、商品に対してかかっているフィルターを取り除きます
そうすることで分母を常に正しく計算することができます
ちなみにALL関数を使うと時系列でのフィルターがかからないため、分母が異常に大きくなってしまいます
SUMMARIZECOLUMNS関数～簡単にグループ化～
SUMMARIZECOLUMNS関数を使用すると、下のようなデータを簡単にグループ化することができます
新しいテーブルからこのDAX関数を使用してみます
yyyyMM列をグループ化の切り口として「月小計」という列を作成し、「月小計」に受注数のグループ別の小計を計算します
NewTable=SUMMARIZECOLUMNS(①’OrderData'[yyyyMM],②”月小計”,③SUM(‘OrderData'[受注数]))
たった３つの引数だけで作成できるのでとても便利です
①：グループ化の切り口、②新列名、③計算式
こちらの関数は次のように変数にSUMMARIZECOLUNS関数で作成したテーブルを代入して使用することもできます
Sales_MaxMonth = VAR myTable=SUMMARIZECOLUMNS(‘OrderData'[yyyyMM],”月小計”,SUM(‘OrderData'[受注数]))
VAR MaxMonth=MAX(‘OrderData'[yyyyMM])
RETURNSUMX(FILTER(myTable,[yyyyMM]=MaxMonth),[月小計])
CALCULATE関数にて複数条件を指定する
CALCULATE関数は条件を指定しながら様々な集計ができる便利なDAX関数です
複数条件を指定する場合には次のような式になります
AND条件
CALCULATE関数に条件を追加する場合には「コンマ」を追加します
例えば、下の画像のデータから(商品＝A)and (月=202303)という条件で合計金額を集計したいとします
その場合のメジャーは次のようになります
商品：A and 月：202303 = CALCULATE(SUM(‘DAX1′[金額]),’DAX1′[商品]=”A”,’DAX1′[月]=202303)
OR条件
OR条件の場合には「||」を使用します
商品：A or 商品：B = CALCULATE(SUM(‘DAX1′[金額]),’DAX1′[商品]=”A” || ‘DAX1′[商品]=”B”)
この場合、「in」を使用するという方法も使えます
商品：A or 商品：B_in使用 = CALCULATE(SUM(‘DAX1′[金額]),’DAX1′[商品] in {“A”,”B”})
SWITCH関数とTRUEの組み合わせ
通常
SWITCH関数は場合分けをするケースでとても便利です
注文金額3 = SWITCH(‘分析用2′[注文金額],1200,”1200″,8600,”8600″,”X”)
IF関数と比べて条件を複数追加することができます
ただ条件設定が「＝しか指定できない」、「指定順序が間違いやすい」などの問題があります
TRUE
SWITCH関数の第一引数を「TRUE」とすると、もっと柔軟に条件を指定できるようになります
注文金額2 = Switch(true(),[注文金額]<2000,”2000未満”,[注文金額]<4000,”4000未満”,”4000以上”)
