カテゴリー別アーカイブ: DAX関数

コンテキスト遷移~DAXの何故こうなるのか?を解決する~

【このコンテキスト遷移の理解がDAX自体の理解の近道です】

DAXを使用していて「何故こうなるの?」ということに出会うことがあります

「何故こうなるの?」の不思議の一つが「コンテキスト遷移」です

早速、事例で見てみましょう

上の画像の2つの列は「大阪」で値が2300と1200と言う風に違いがあります

実際にメジャーの中身を見ると些細な違いしかありません

・MAXX_NoCal

  MAXX_NoCal = MAXX(‘担当者’,SUMX(‘売上’,’売上'[金額]))

・MAXX_withCal

  MAXX_withCal = MAXX(‘担当者’,CALCULATE(SUMX(‘売上’,’売上'[金額])))

MAXXの第二引数にCALCULATE関数があるかないかだけです

ところが、このCALCULATE関数のあるなしがとても大きいのです

CALCULATE関数があるメジャーは2300⇒1200となり、何らかの形でデータの絞り込み(フィルター)が行われています

今回の記事ではこの絞り込みが有効になる「コンテキスト遷移」の仕組みについて解説していきます

DAXで計算したいこと

今回は2つのテーブルを使用します

1つ目は担当者のテーブル、2つ目は売上テーブルです

売上テーブルは同一担当者が複数登場しています

DAXを使用して、この2つのテーブルを組み合わせて、東京と大阪で最大の売上をあげた担当者の金額を計算します

MAX関数での計算

「東京と大阪で最大の売上・・・」というように「最大」というキーワードが出てくるので、MAX関数を使用するのでは・と思った読者の方も多いと思います

では早速、MAX関数で計算してみましょう

MAX = MAX(‘売上'[金額])

そうすると上の画像のように、思ったよりも小さい金額算出されます

これはそもそも集計の単位が違っています

ですので、まずは担当者テーブルで新しい列を作成し、担当者別に売上金額を算出してみます

新しい列で担当者別の売上を算出した上で、MAX関数を使用してみます

これで意図とした通りの結果が出ました

ポイントは新しい列を作成する時、CALCULATE関数を使用していることです

画像に alt 属性が指定されていません。ファイル名: image-63.png

仮に、上の画像のCALCULATE関数を取り除いたらどうなるでしょうか?

全ての行の合計が算出されてしまいます

つまり、CALCULATE関数を使うことで遷移(全ての行⇒行別)が起こっているのです

MAXX関数/行評価

上のMAX関数の事例ではメジャーを作成するまえに、新しい列を作成しました

今度はMAXX関数を使用して、直接メジャーで担当者ごとの最高金額を大阪と東京で算出したいと思います

MAXX関数では単純に最大値を算出するのではなく、行別に式を評価した上で最大値を算出します

他の言葉で言い換えると、条件に合う「行の中」で最大値を算出してくれます

ちなみに、大阪と東京の合計金額は次の画像のようになっています

では、話を戻してMAXX関数を実際に使用して大阪と東京の担当者別の最大値を算出してみましょう

通常の合計金額と同じ結果になってしまっています

つまり、大阪もしくは東京という「行別」だけが考慮された計算金額になってしまっています

MAXX関数の第一引数はMAXX(’担当者’となっているのに「担当者の行別」は考慮されていません

これは、前に例に挙げた下の画像と同じ現象が起こっています

画像に alt 属性が指定されていません。ファイル名: image-65.png

この時はCALCULATE関数を使用して「行別への遷移」を起こしました

実は、今回も同じようにCALCULATE関数を使用すれば正しく算出されます

CALCULATE関数を使用することで、今回もMAXX関数内で「行別への遷移」を起こしているわけです

これで、DAXは何故こうなるのか?の謎が一つが解けたと思います

メジャー使用による行評価

番外編として、次の事例も紹介したいと思います

上の画像の一番右の式はCALCULATE関数を使用していません

MAXX_NoCal_withMEASURE = MAXX(‘担当者’,[売上合計])

その代わり、第二引数に[売上合計]というメジャーを使用しています

ですが、CALCULATE関数を使用したのと同じ結果がでています

これはメジャーを使うこと自体で、CALCULATE関数を使用するのと同等の効果があることを意味します

たとえメジャーの中身と同じ内容を関数の引数で使用しても、違う結果が出てしまうのはこの為です

<まとめ>

今回はCALCULATE関数を使用して、行別の評価を関数内で受け渡す「コンテキスト遷移」について解説しました

この概念を理解すると、格段にDAXの活用の幅が広がります

ぜひマスターしておきましょう!!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

DAX関数の虎の巻

こちらのコーナーでは、DAX関数*をうまく使うためのコツを解説します

*エクセルのワークシートでは関数に相当するもの

フィルター評価と行評価

フィルター評価

Power BIのレポート画面では常にフィルター評価の影響を受けます

この点は常に意識しておく必要があります

例えば、次のようなデータがあったとします

こちらのデータをマトリックスで表示すると次のようになります

こちらのマトリックスの行にカラーを入れてみます

こちらは行単位で青、赤、緑のフィルターが適用されています

上の画像では、スライサーのフィルターが適用されています

このように、レポート画面ではフィルターの影響を常に受けるわけですが、DAXも同様です

但し、CALCULATEをDAX式で活用するとルールが変わります

試しに、青色の金額を抽出するCALCULATE関数でメジャーを書いてみます

Color_BLUE = CALCULATE(SUM(myTable[金額]),’myTable'[カラー]=”青”)

スライサーのフィルターを解除した上で、上記のメジャーをマトリックスに配置してみます

マトリックスの行単位での色別のフィルターは解除され、全て同じ値/6,600になっています

今度は、スライサーのフィルターを適用してみます

DAX外のフィルターはきちんと適用されているのが分かります

今度はメジャーにKEEPFILTERS関数を適用してみます

今度は、行単位での色別のフィルターが適用されています

以上、レポート画面では常にフィルターの影響を受ける点とCALCULATE関数を使うとルールが変わる点は常に意識しておきましょう!

行評価

フィルター評価と同じくらい重要で、フィルター評価より難解なのが行評価です

こちらは、明確にフィルター評価と区別して理解しましょう

例えば、次のようなデータがあるとします

こちらのデータから、行毎に値1と値2を乗算する列を作成してみます

次に値1x値2の列を合計する列を作成します

では上の画像と同じ「55」を算出するメジャーを、新しく作成した列をつかわないまま作成するにはどうしたらいいでしょう?

次のように式を書くと、突拍子もない数字になります

これは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に相当します

そしてD2セルで下の画像のようにmyValueを使ってみます

このD2セルの3がRETURNになります

ちなみに、B2セルの値のmyValueを2から3に変えるとD4セルのリターンは4になります

では、上記と同じ内容をPower BI内でも行ってみます

上の画像では、myValueという箱に2を入れ、更に1を加えてリターンしています

では、myValueを3にすると4がリターンします

実際には、このVARを多数組み合わせて使う形になります

ALLとALLSELECTEDとの使い分け~構成比の罠~

ALLALLSELECTEDの違いがDAXを使用したばかりだとよくわからないと思います

そもそも、ALLって何のためにあるの?という方も中にはいらっしゃると思います

ALLはスライサーなどでフィルター処理が行われた場合のためにあります

上のGIF画像では、分子がフィルターされた場合にも、分母の全体売上はALL関数により変わらないようになっています

ところが、分母の内容自体がフィルターされる場合が厄介です

上の画像では、構成比の分母は各都道府県の合計になります

仮に3つの都道府県にフィルターした場合には次のようになります

構成比_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関数です

次の表を見てください

値1の列と値2の列をそれぞれ掛け合わせ、E8セルで合計しています

E10セルにはSUMPRODUCT関数が入っており、列1と列2をそれぞれ乗じつつ、各行の結果を合計しています

H3セルにはSUMIFS関数が入っており、G3セルの条件に応じて、列1と列2を乗じた値の合計を計算しています

但し、Eの列で算出した値を参照しています

SUMPRODUCT関数のように、単独では計算できません

DAXのSUMX関数では、このSUMPRODUCT関数とSUMIFS関数の組み合わせを一発で計算できます

SUMX関数の文法は次のようになります

SUMX(テーブル,式)

試しに、前述のSUMPRODUCT関数と同じことをしてみます

元データは前と同じデータ(テーブル名:myTable)になります

下の画像では、myTableを第一引数のテーブルとして指定し、第二引数で式を指定しています

mySUMX = SUMX(‘myTable’,’myTable'[値1]*’myTable'[値2])

今度は、SUMIFS関数と同じように列1の値が<3とします

この場合は、第一引数のテーブルに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関数の事例1です

mySummarize = SUMMARIZE(‘売上データ3’,’売上データ3′[都道府県],”Revenue”,SUM(‘売上データ3′[売上金額]),”AVERAGE”,AVERAGE(‘売上データ3′[売上金額]))

事例2として集計の切り口を都道府県のみから、商品も加えてみましょう

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式と組み合わせて空欄を0に置き換えています

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'[受注数]))

たった3つの引数だけで作成できるのでとても便利です

①:グループ化の切り口、②新列名、③計算式

こちらの関数は次のように変数に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以上”)

RANKX関数~順位付け~

DAXのRANKX関数を使えば、集計した値に対して順位付けが行えますのでとても便利です

RANKX関数の文法自体はとても簡単なものですが、実際には少々トリッキーな面があります

文法:RANKX(使用テーブル,順位を判断する値)

*実際には他にも指定できます

作成してみたけど結果が全て1になってしまった、という方も中にはいらっしゃると思いますので、トリッキーな面を中心に解説を行います

解説に使用するテーブル/テーブル名:Tableは以下です。カテゴリーが大小2つあります

こちらのデータから大、小のカテゴリーにて順位付けを行えるようにします

判断に使用する値は予めメジャーで算出しておきます

これが1つ目の大きなポイントです

次に実際にカテゴリー大を順位付けするメジャーを作成します

ここでAllを使用するのが2つ目の大きなポイントです

そして、Allの引数はカテゴリー列も指定します

Allを使用しないと、ビジュアルで表示する時にフィルターが効いて全て1になります

他の言葉で言い換えると、フィルターが効いても他の行の値と比較して順位付けできるようにします

では次にカテゴリー小を順位付けするメジャーを作成します

このメジャーをマトリックス表にセットしてみます

こちらは大カテゴリー内での順位が出力されます

今度は、カテゴリー大の関連はマトリックス表から抜いてみます

これでカテゴリー小の間で順位付けが行えました

繰り返しになりますが、順位付けを行う値は事前にメジャーにしておきましょう!

DISTINCTCOUNTROWS vs DISTINCT vs VALUES

一見、同じに見えるこれらの関数ですが、何が違うのでしょうか?

試しに、これらの3つの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から2019年7月に注文があった会員の番号をリスト化しています

こんな風に1列をリスト化するVALUES関数と組み合わせて、条件に合うリストを作るケースなどで便利です

更には「VAR~RETURN」と組み合わせて、CalculateTable関数で出力したリストから更に別なものを出力するといった事もできます

EOMONTH関数~時系列で日付を整理する~

時系列で日付を整理するのに便利なのが、EOMONTH関数です

例えば前月末日を知りたいとします

その場合は次のようにメジャーを書きます

これだけで自動的に前月末日が出力されます

では、前月開始日を出したい場合にはどうしたらいいでしょうか?

その場合は「+1」をうまく使います

これで、開始日も自由に抽出できます

フィルターが複数あるケースで割合を計算する~Calculate&AllSelected&removeFilters~

よくこんな風にフィルターがスライサーを含めて複数かかるケースがあります

上の画像のケースでは「日付、しかも月」「商品」の2つです

この場合、グラフ内の選択商品の割合をどうやって計算しますか?

下の画像の場合は商品A~Cの合計が100で、選択された商品Bは45なので45%です

分子は該当の「月」で該当の「商品」の売上金額です

つまり純粋に選択されたものです

ですので以下のメジャーで簡単に算出されます

CALCULATE(SUM(‘テーブル'[売上金額]),ALLSELECTED(‘テーブル’))

では、分母はどうでしょう?意外と難しく感じられませんでしょうか?

フィルターは「月」だけです

ですが、Calculate関数内で指定できるのは「日付単位」です

この場合は、Calculate関数内で次のようにRemoveFilters関数を組み合わせればあっさりと解決します

CALCULATE(sum(‘テーブル'[売上金額]),ALLSELECTED(‘テーブル’),REMOVEFILTERS(‘テーブル'[商品]))

こちらが割合を計算するメジャーです

割合 =

VAR bunshi=CALCULATE(SUM(‘テーブル'[売上金額]),ALLSELECTED(‘テーブル’))

VAR bunbo=CALCULATE(

            sum(‘テーブル'[売上金額]),

            ALLSELECTED(‘テーブル’),

            REMOVEFILTERS(‘テーブル'[商品])

      )

RETURN DIVIDE(bunshi,bunbo)

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ