DAX式サンプル
よく使われるDAX式のサンプルです。時々更新します。
なお、ページ数は私の著作『Excelパワーピボット』のでの参照ページです。
[P194] 売上合計
=
SUM('F_売上明細'[売上])
[P197] 平均単価（割り算を行うときはDivide By ZeroエラーはNULLに）
=
DIVIDE([売上合計],[販売数量合計])
[P218] 商品売上総計（テーブル単位でのフィルター解除）
=
CALCULATE(
[売上合計],
ALLSELECTED('商品')
)
[P228] 商品カテゴリー売上小計（テーブルの列単位でのフィルター解除）
=
CALCULATE(
[売上合計],
ALLSELECTED('商品'[商品名])
)
[P233] 商品カテゴリー選択（現在当たっているフィルターを検知）
=
ISFILTERED('商品'[商品カテゴリー])
[P235] 商品割合（ドリルダウンのレベルごとにメジャーの表示を切り替える）
=
IF(
ISFILTERED('商品'[商品名])=TRUE, [商品売上小計割合],
[商品カテゴリー割合]
)累計売上（FILTER関数）
=
CALCULATE (
[売上合計],
FILTER (
ALL('カレンダー'),
'カレンダー'[日付] <= MAX('カレンダー'[日付])
)
)
[P254] 当期累計売上①詳細パターン
=
CALCULATE (
[売上合計],
FILTER (
ALL('カレンダー'),
'カレンダー'[日付] <= MAX('カレンダー'[日付])
&& 'カレンダー'[会計年度] = MAX('カレンダー'[会計年度])
)
)
当期累計売上（FILTER関数＆VAR）
=
VAR DateFilter
=
FILTER (
ALL('カレンダー'),
'カレンダー'[日付] <= MAX('カレンダー'[日付])
&& 'カレンダー'[会計年度] = MAX('カレンダー'[会計年度])
)
RETURN
CALCULATE (
[売上合計],
DateFilter
)
[P256] 当期売上累計②タイムインテリジェンス関数
=
CALCULATE(
[売上合計],
DATESYTD('カレンダー'[日付], "3/31")
)
=
CALCULATE(
[売上合計],
DATESQTD('カレンダー'[日付])
)
=
CALCULATE(
[売上合計],
DATESMTD('カレンダー'[日付])
)
[P263] 前年度売上合計（SAMEPERIODLASTYEAR）
=
CALCULATE(
[売上合計],
SAMEPERIODLASTYEAR('カレンダー'[日付])
)
前年度売上（FILTER関数＆VAR＆ISFILTERED＆IF）
[売上合計_前年_年]
=VAR DateFilter
=
FILTER (
ALL('カレンダー'),
'カレンダー'[会計年度] = MAX('カレンダー'[会計年度]) -1
)
RETURN
CALCULATE (
[売上合計],
DateFilter
)
[売上合計_前年_Qtr]
=VAR DateFilter
=
FILTER (
ALL('カレンダー'),
'カレンダー'[会計年度] = MAX('カレンダー'[会計年度]) -1 &&
'カレンダー'[Qtr] = MAX('カレンダー'[Qtr])
)
RETURN
CALCULATE (
[売上合計],
DateFilter
)
[売上合計_前年_月]
=VAR DateFilter
=
FILTER (
ALL('カレンダー'),
'カレンダー'[会計年度] = MAX('カレンダー'[会計年度]) -1 &&
'カレンダー'[月] = MAX('カレンダー'[月])
)
RETURN
CALCULATE (
[売上合計],
DateFilter
)
[売上合計_前年_日付]
=VAR DateFilter
=
FILTER (
ALL('カレンダー'),
'カレンダー'[会計年度] = MAX('カレンダー'[会計年度]) -1 &&
'カレンダー'[YTD] = MAX('カレンダー'[YTD])
)
RETURN
CALCULATE (
[売上合計],
DateFilter
)
[売上合計_前年]
=
IF (
ISFILTERED ( 'カレンダー'[日付] ) = TRUE,
[売上合計_前年_日付],
IF (
ISFILTERED ( 'カレンダー'[月] ) = TRUE,
[売上合計_前年_月],
IF (
ISFILTERED ( 'カレンダー'[会計四半期] ) = TRUE,
[売上合計_前年_Qtr],
IF (
ISFILTERED ( 'カレンダー'[会計年度] ) = TRUE,
[売上合計_前年_年],
BLANK ()
)
)
)
)
[P263] 前年度売上累計（DATESYTDをSAMEPERIODLASTYEARで囲む）
=
CALCULATE(
[売上合計],
SAMEPERIODLASTYEAR(
DATESYTD('カレンダー'[日付],"3/31")
)
)
3カ月移動平均 DATESINPERIODを使用
=
CALCULATE (
[売上合計],
DATESINPERIOD ( 'カレンダー'[日付], MAX ( 'カレンダー'[日付] ), -3, MONTH )
)
ランキング（ALLと組み合わせて使用する）
=
RANKX(
ALLSELECTED('商品'), [売上合計]
)
=
IF(
[売上合計],
RANKX(
ALLSELECTED('支店'), [売上合計]
)
)
値表示（ VALUES は列の値を列挙するが、1行のみのときスカラーになる）
COUNTROWSによるパターン
=
IF (
COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) ) = 1,
VALUES ( 'P_集計タイプ'[集計タイプ] )
)
HASONEVALUEによるパターン
=
IF (
HASONEVALUE ( 'P_集計タイプ'[集計タイプ] ),
VALUES ( 'P_集計タイプ'[集計タイプ] )
)
Power BIのみ
= SELECTEDVALUE('P_集計タイプ'[集計タイプ] )
スライサーとパラメーターテーブルによる選択
リレーションシップを持たない分類だけのテーブルを作り、スライサー経由でフィルターコンテキストを渡して、表示するメジャーを切り替える
COUNTROWSによるパターン
=
IF (
COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) ) = 1,
SWITCH (
VALUES ( 'P_集計タイプ'[集計タイプ] ),
"売上", [売上合計],
"原価", [売上合計] - [利益合計],
"利益", [利益合計]
)
)
HASONEVALUEによるパターン
=
IF (
HASONEVALUE ( 'P_集計タイプ'[集計タイプ] ),
SWITCH (
VALUES ( 'P_集計タイプ'[集計タイプ] ),
"売上", [売上合計],
"原価", [売上合計] - [利益合計],
"利益", [利益合計]
)
)
Power BIのみ
=
SWITCH (
SELECTEDVALUE('P_集計タイプ'[集計タイプ] ),
"売上", [売上合計],
"原価", [売上合計] - [利益合計],
"利益", [利益合計]
)セグメンテーション（値を元にAランク、Bランクというように分類）
区分化テーブルにリレーションシップは作らず、不等号によるレンジでセグメントを返す。
メジャー1レベル
Grade =
CALCULATE(
VALUES( '区分化テーブル'[Rank] ),
FILTER( '区分化テーブル', '区分化テーブル'[Start] < [売上合計] && [売上合計] < '区分化テーブル'[End] )
)
メジャー2レベル
Grade_2 =
VAR PRODUCT_CATEGORY = SELECTEDVALUE('商品'[商品カテゴリー])
RETURN
CALCULATE (
VALUES ( '区分化テーブル'[Rank] ),
FILTER ( '区分化テーブル', '区分化テーブル'[Start] < [売上合計] && [売上合計] < '区分化テーブル'[End] ),
FILTER ( '区分化テーブル', '区分化テーブル'[Category] = PRODUCT_CATEGORY )
)
デバッグ式
タイムインテリジェンス関数（シリアル値による最大・最小）
=
// カレンダー
VAR DateFilter = SAMEPERIODLASTYEAR('カレンダー'[日付])
// Date Range
VAR MaxDate = FORMAT(CALCULATE(MAX('カレンダー'[日付]), DateFilter), "YYYY/MM/DD")
VAR MinDate = FORMAT(CALCULATE(MIN('カレンダー'[日付]), DateFilter), "YYYY/MM/DD")
VAR CountDates = COUNTROWS(DateFilter)
RETURN
MinDate & " - " & MaxDate & " (" & CountDates & ")"
FILTER関数（シリアル値による最大・最小）
=
// カレンダー
VAR DateFilter
= FILTER(
ALL('カレンダー'),
'カレンダー'[日付] <= MAX('カレンダー'[日付]) &&
'カレンダー'[会計年度] = MAX('カレンダー'[会計年度])
)
// Date Range
VAR MaxDate = FORMAT(CALCULATE(MAX('カレンダー'[日付]), DateFilter), "YYYY/MM/DD")
VAR MinDate = FORMAT(CALCULATE(MIN('カレンダー'[日付]), DateFilter), "YYYY/MM/DD")
VAR CountDates = COUNTROWS(DateFilter)
RETURN
MinDate & " - " & MaxDate & " (" & CountDates & ")"
テキスト値（最大・最小を持たないので列挙する）
=
CONCATENATEX('商品', '商品'[商品名] , " / ")
メジャーによるカテゴリー別累積計算テーブル（以下リンクを参考にしたものです）
ABC dynamic classification – DAX Patterns
テーブル（Power BIのみ）
=
ADDCOLUMNS(
-- レベル１：商品ID、売上合計
ADDCOLUMNS( VALUES( '商品'[商品ID] ), "商品ごと売上", [売上合計] ),
-- レベル１：追加項目
"順位", COUNTROWS(
-- サブテーブルの作成
FILTER(
-- レベル２：商品ID、売上合計
ADDCOLUMNS( VALUES( '商品'[商品ID] ), "サブ商品ごと売上", [売上合計] ),
-- 外側の１行１行に対して内側の方が大きいものに絞る
[サブ商品ごと売上] >= [商品ごと売上]
)
),
-- レベル１：追加項目
"累積", -- 上位からの合計
SUMX(
-- サブテーブルの作成
FILTER(
-- レベル２：商品ID、売上合計
ADDCOLUMNS( VALUES( '商品'[商品ID] ), "サブ商品ごと売上", [売上合計] ),
-- 外側の１行１行に対して内側の方が大きいものに絞る
[サブ商品ごと売上] >= [商品ごと売上]
),
-- サブテーブルの売上を合計
[サブ商品ごと売上]
),
-- レベル１：追加項目
"商品売上 Total", CALCULATE( [売上合計], VALUES( '商品'[商品ID] ) ),
-- レベル１：追加項目
"累積割合", -- 上位からの合計
DIVIDE(
SUMX(
-- サブテーブルの作成
FILTER(
-- レベル２：商品ID、売上合計
ADDCOLUMNS( VALUES( '商品'[商品ID] ), "サブ商品ごと売上", [売上合計] ),
-- 外側の１行１行に対して内側の方が大きいものに絞る
[サブ商品ごと売上] >= [商品ごと売上]
),
-- サブテーブルの売上を合計
[サブ商品ごと売上]
),
CALCULATE( [売上合計], VALUES( '商品'[商品ID] ) )
)
)メジャーによるABC分析（以下リンクを参考にしたものです）
ABC dynamic classification – DAX Patterns
売上合計 ABC =
CALCULATE(
[売上合計],
VALUES( '商品'[商品ID] ),
-- 商品IDごとのA,B,Cに限定
FILTER(
CALCULATETABLE(
ADDCOLUMNS(
-- レベル１：商品ID、売上合計
ADDCOLUMNS(
VALUES( '商品'[商品ID] ),
"商品ごと売上", [売上合計]
),
-- レベル１：追加項目
"累積割合", -- 上位からの合計
DIVIDE(
SUMX(
-- サブテーブルの作成
FILTER(
-- レベル２：商品ID、売上合計
ADDCOLUMNS(
VALUES( '商品'[商品ID] ),
"サブ商品ごと売上", [売上合計]
),
-- 外側の１行１行に対して内側の方が大きいものに絞る
[サブ商品ごと売上] >= [商品ごと売上]
),
-- サブテーブルの売上を合計
[サブ商品ごと売上]
),
CALCULATE(
[売上合計],
VALUES( '商品'[商品ID] )
)
)
),
-- 商品テーブルのフィルターを解除
ALL( '商品' )
),
-- FILTERでレンジを限定
[累積割合] > [区分下限値]
&& [累積割合] <= [区分上限値]
)
)
ある月に売上のない顧客ID（計算テーブル）
顧客ID売上無し =
FILTER (
ADDCOLUMNS (
VALUES ( 'F_売上明細'[顧客ID] ),
"前回の売上",
CALCULATE (
COUNTROWS ( 'F_売上明細' ),
(
FILTER (
ALL ( 'カレンダー' ),
'カレンダー'[月] = 9
)
)
)
),
[前回の売上] = 0
)
売上無し顧客名（リレーションシップ経由）
顧客名売上無し =
FILTER (
ADDCOLUMNS (
// VALUES ( 'F_売上明細'[顧客ID] ),
VALUES ( 'D_顧客'[顧客名] ),
"前回の売上",
CALCULATE (
COUNTROWS ( 'F_売上明細' ),
(
FILTER (
ALL ( 'カレンダー' ),
'カレンダー'[月] = 9
)
)
)
),
[前回の売上] = 0
)
新規顧客数（メジャー）
新規顧客数 =
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( 'F_売上明細'[顧客ID] ),
"前回の売上",
CALCULATE (
COUNTROWS ( 'F_売上明細' ),
(
FILTER (
ALL ( 'カレンダー' ),
'カレンダー'[日付] < MIN( 'カレンダー'[日付])
)
)
)
),
[前回の売上] = 0
)
)
新規顧客数（前年売上無し）
新規顧客数（前年売上無し） =
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( 'F_売上明細'[顧客ID] ),
"前回の売上",
CALCULATE (
COUNTROWS ( 'F_売上明細' ),
(
FILTER (
ALL ( 'カレンダー' ),
'カレンダー'[会計年度] = SELECTEDVALUE('カレンダー'[会計年度]) -1
)
)
)
),
[前回の売上] = 0
)
)
Many to Many の集計
TREATAS_売上 = CALCULATE(
[売上合計],
TREATAS( VALUES('P_顧客と支店の組み合わせ'[支店]), 'D_支店'[支店名]),
TREATAS( VALUES('P_顧客と支店の組み合わせ'[顧客]), 'D_顧客'[顧客ID])
)
