More than 1 year has passed since last update.

雑・Excel入門試論 - 脱VLOOKUPの思考Advent Calendar 2022

雑・Excel入門試論 - 脱VLOOKUPの思考 22 DAX - CALCULATE

Last updated at 2022-12-27Posted at 2022-12-21

（※この記事は2022年12月21日に書かれています。Excelのバージョンは、Microsoft® Excel® for Microsoft 365 MSO (バージョン 2212 ビルド 16.0.15928.20144) 64 ビットです）

　フィルターコンテキストの動きを理解する上で、CALCULATE 関数を知らなければなりません。

基本

CALCULATE構文

CALCULATE(
    <expression>                       // 評価される式
    [, <filter1> [, <filter2> [, …]]]  // フィルター
)

　第1引数は式またはメジャーになります。第2引数はフィルターです。

ブール型フィルター

　True/Falseの条件によってフィルターを行います。

Bool1

Bool1:=CALCULATE(
    SUM(financials[Sales]),
	financials[Product] = "Paseo"
)

　上記は、製品名が「Paseo」の売上を計算しています。

　次に、最終日の売上を計算させてみます。

Bool2_NG

Total Sales on the last date = 
CALCULATE(
    SUM(financials[Sales]), 
    financials[Date] = MAX(financials[Date])
)

　この式はうまく動きそうですが、以下のようなエラーが出ます。

2022年12月21日現在では、ExcelのDAXでは、CALCULATEのフィルターにスカラー値を返す集計関数を含めることはできません。

そこで以下のように書き換えます。

Bool2_OK

Total Sales on the last date:=
VAR _LastDate = MAX(financials[Date])  // 最終日を_LastDateへ代入
RETURN
CALCULATE(
	SUM(financials[Sales]),
	financials[Date] = _LastDate      // 日付が最終日でフィルター処理
)

　ここで、DAX式の変数の扱いを説明します。

変数

メジャー名 :=
VAR 変数名 = 式
RETURN
  式

　VAR の後には変数名と式を書き、RETURN以降にメジャーで計算される式を書きます。変数はいくつでも書くことができます。

　Power BIでは、2021年9月のリリースでスカラー値を返す集計関数を含めることができるようになっています。

　このように、ExcelのDAXは、Power BIよりリリースが遅く、動作が異なる場合があります。

テーブルフィルター

　テーブル式フィルターでは、テーブルオブジェクトがフィルターとして適用されます。

　先の例で、最期の日付をMAXで取得して計算する方法がありましたが、変数を使わずに最期の日付をテーブル型で取得して計算する方法があります。

TableFilter

TableFilter := CALCULATE(
    SUM(financials[Sales]),
    LASTDATE(financials[Date])
)

　MAX はスカラー値を返す関数ですが、LASTDATE は日付列の最期の日付を1列1行のテーブルで返します。結果は同じになりますが、こちらはテーブルフィルターを使った計算式になります。

フィルター関数

関数	名称	説明
ALL	All	指定されたテーブル、または列の全てのフィルターを削除します
ALLEXCEPT	All Excpet	指定された列を除く他の列のフィルターを削除します
ALLNOBLANKROW	All No Blank Row	指定されたテーブル、または列の空白を除く全てのフィルターを削除します
KEEPFILTERS	Keep Filters	既存のフィルターを削除せずにフィルターを追加します
USERELATIONSHIP	Use Relationship	非アクティブなリレーションシップを使用し、干渉するリレーションシップを非アクティブにします。
CROSSFILTER	Crossfilter	フィルター方向を変更します

Excelで未サポートの関数

関数	名称	説明
REMOVEFILTERS	Remove Filters	指定されたテーブル、または列からフィルターを削除します

ALL

　ALLを使った例です。

CALCULATE_ALL

CALCULATE_ALL:=CALCULATE(
    [Sales],
    FILTER(
        ALL(financials[Product]),
        financials[Product] = "Paseo"
    )
)

　この式では、フィルターにテーブルを使っているので、テーブルフィルターと呼びます。そして、 FILTER 関数は、financials[Product] のフィルターを削除しているので、製品名でフィルターした表はすべて同じ値になってしまいます。そして、これは、上のBool1で書いたDAX式と同じ結果を示します。

　つまり、CALCULATE のフィルターで書かれた列は、外のフィルターを削除してしまいます。

KEEPFILTER

　外のフィルターを削除せずに集計を行いたい場合は、KEEPFILETERS を使用します。

CALCULATE_KEEPFILTERS

CALCULATE_KEEPFILTER := CALCULATE(
	SUM(financials[Sales]),
	KEEPFILTERS(
        financials[Product]="Paseo"
    )
)

　これは、期待通りの動きをしています。

CALCULATE_FILTER

CALCULATE_FILTER := CALCULATE(
    SUM(financials[Sales]),
    FILTER(
        financials, 
        financials[Product]="Paseo"
    )
)

　これも、先程と同じ期待通りの動きをしますが、ここで行われているのは、テーブル全てのフィルターを消して計算を行っており、より多くのリソースが使用されています。パフォーマンスの良い計算を行うためには、フィルターの削除を行う場合にテーブル全体ではなく、必要な列に絞って行うことが大切です。

ALLEXCEPT

　製品売上の割合を見たい場合、以下のような式を使います。

Pct := DIVIDE(
    SUM(financials[Sales]),
    CALCULATE(
        SUM(financials[Sales]),
        ALL(financials)
    )
)

　では、国ごとの製品売上の割合が見たい場合、どのようにするのでしょうか。行にCountryを追加しただけでは、以下のようになります。

　分母は国ごとの売上合計になる必要があるので、REMOVEFILTERSを使って REMOVEFILTERS(financials[Product]) とやりたいとこですが、ExcelではREMOVEFILTERSが使えないので、ALLEXCEPTを使います。

ALLEXCEPT

AllExcept := DIVIDE(
    SUM(financials[Sales]),
    CALCULATE(
        SUM(financials[Sales]),
        ALLEXCEPT(financials,financials[Country])
    )
)

　これで、以下のような表が出来上がります。

ALLSELECTED

　フィルターを設定して、表示する製品を選ぶようにしたいと思います。その場合、先程の式だと以下のようになってしまいます。

　選択された製品で100%になるように表示したいので、ALLSELECTED を使用して、外部のフィルターが有効になるように計算します。

AllSelected

AllSelected :=DIVIDE(
    SUM(financials[Sales]),
    CALCULATE(
        SUM(financials[Sales]),
        ALLSELECTED(financials[Product])
    )
)

　外部のフィルターを生かして計算することができました。

IN

　複数の製品を選択してフィルターしたい場合は、IN を使用することができます。

CalculateIn

CalculateIn := CALCULATE(
    SUM(financials[Sales]),
    financials[Product] IN {"Paseo", "Amarilla"}
)

　この結果は、以下のようになってしまいます。

Paseo と Amarilla の合計値が全てのセルに表示されています。外のフィルターを活かすためには、KEEPFILTER を使います。

CalculateIn

CalculateIn := CALCULATE(
    SUM(financials[Sales]),
    KEEPFILTERS(
        financials[Product] IN {"Paseo", "Amarilla"}
    )
)

コンテンツ

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up