こちらのコーナーでは、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・・・)
早速、使用例を見てみましょう
元のデータは次の通りです
売上データ3
これが上の画像のテーブルから都道府県別の売上合計、平均を算出する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])
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以上”)
RANKX関数～順位付け～
DAXのRANKX関数を使えば、集計した値に対して順位付けが行えますのでとても便利です
RANKX関数の文法自体はとても簡単なものですが、実際には少々トリッキーな面があります
文法：RANKX(使用テーブル,順位を判断する値)
*実際には他にも指定できます
作成してみたけど結果が全て１になってしまった、という方も中にはいらっしゃると思いますので、トリッキーな面を中心に解説を行います
解説に使用するテーブル/テーブル名：Table は以下です。カテゴリーが大小２つあります
こちらのデータから大、小のカテゴリーにて順位付けを行えるようにします
判断に使用する値は予めメジャー で算出しておきます
これが1つ目の大きなポイントです
次に実際にカテゴリー大を順位付けするメジャーを作成します
ここでAllを使用するのが2つ目の大きなポイントです
そして、Allの引数はカテゴリー列も指定します
Allを使用しないと、ビジュアルで表示する時にフィルターが効いて全て１になります
他の言葉で言い換えると、フィルターが効いても他の行の値と比較して順位付けできるようにします
では次にカテゴリー小を順位付けするメジャーを作成します
このメジャーをマトリックス表にセットしてみます
こちらは大カテゴリー内での順位が出力されます
今度は、カテゴリー大の関連はマトリックス表から抜いてみます
これでカテゴリー小の間で順位付けが行えました
繰り返しになりますが、順位付けを行う値は事前にメジャーにしておきましょう！
DISTINCTCOUNTROWS vs DISTINCT vs VALUES
一見、同じに見えるこれらの関数ですが、何が違うのでしょうか？
試しに、これらの３つのDAX関数を使用して下の画像のテーブルにて「客先名」をカウントするメジャーを作成してみたいと思います
実際に作成するメジャーの中身は次の通りとなります
ちなみにDISTINCT関数 とVALUES関数 はCOUNTROWS関数 と組み合わせます
CountBY DistinctCount = DISTINCTCOUNT (‘売上テーブル'[客先名])
CountBY Distinct = COUNTROWS(DISTINCT(‘売上テーブル'[客先名]))
CountBY Values = COUNTROWS(VALUES(‘売上テーブル'[客先名]))
結果は全く同じ結果になります（空欄もカウントしています）
ではどのような時に結果が違ってくるのでしょうか？
今度は客先マスタで客先名をカウントしてみます
但し、先ほどの売上テーブルとリレーションを組んでおきます
実際に作成するメジャーは次の通りとなります
CountBY DistinctCount = DISTINCTCOUNT(‘客先マスタ'[客先名])
CountBY Distinct = COUNTROWS(DISTINCT(‘客先マスタ'[客先名]))
CountBY Values = COUNTROWS(VALUES(‘客先マスタ'[客先名]))
結果は次の通りとなります
VALUES関数だけ売上テーブルの空欄をカウントしているという結果になりました
CalculateTable関数～テーブルの作成～
テーブルを作成する際に便利なのが、CalculateTable関数 です
CalculateTable関数は条件付で計算をするのに便利なCalculate関数のテーブル版です
文法も一緒で、出力されるのがテーブルというだけです
テーブル関数も幾つかありますが、CalculateTable関数を使うケースは次のようなケースが典型的です
Customer IDs = CALCULATETABLE(VALUES(OrderData1[Customer ID]),OrderData1[yyyy-MM]=”2019-07″)
上のDAX式ではVALUES関数と組み合わせて、注文データ/OrderData1から２０１９年７月に注文があった会員の番号をリスト化しています
こんな風に１列をリスト化するVALUES関数と組み合わせて、条件に合うリストを作るケースなどで便利です
更には「VAR~RETURN」と組み合わせて、CalculateTable関数で出力したリストから更に別なものを出力するといった事もできます
EOMONTH関数～時系列で日付を整理する～
時系列で日付を整理するのに便利なのが、EOMONTH関数 です
例えば前月末日を知りたいとします
その場合は次のようにメジャーを書きます
これだけで自動的に前月末日が出力されます
では、前月開始日を出したい場合にはどうしたらいいでしょうか？
その場合は「＋１」をうまく使います
これで、開始日も自由に抽出できます
にほんブログ村
