最近はお客さんの要望が多くなってきたせいか、良い意味でちょこちょこPower BIを触ってます。以前は在庫分析ばかりやっていたので、モデリングや使うメジャーに偏りが生じていましたが、最近はそれもないということで色んな分析ができてなかなか面白い。今回はそんな中であまりブログでも見かけない時刻をベースとするデータモデルで稼働時間分析をケーススタディとしてPower BIでやってみたいと思います。
※ 一番下からオリジナルデータをダウンロードできます。また、Power Queryのソースコードをコピペすればクエリテーブルを生成でき、テーブル名をしっかり同じものにすればTabular EditorでDAXメジャーをデータモデルにセーブバックできます。
想定事例
今回はEnd-to-End(E2E)ベースで、以下の事例を想定してゼロからPower BIを使ってモデリングするところまで構築してみたいと思います。なお、想定に無理があるのは承知していますが、あくまでデモ&データ取得可能という前提で考えて頂ければと思います。
- 事例
Power BIとPower Appsについて学ぶ(=学習稼働時間)を可視化→時間分析として様々な事例にテンプレとして使用可能。
- DAXによる指標構築
探索的分析のための指標(平均値、累積値等)
- 使用ツール
Power BIのみ
データは自体は非常にシンプルであり、以下の4列のみ。ただし、後述しますが、データには規則性があり、教育担当ID(教育係)は2行で1ペア、必ず上が開始、下が終了と続きます。また、同じ教育担当IDは同じ日に複数出現することがあります。
Power BIを使う人は日付ベースでのモデリングを行うことが多いと思いますが、製造業(ライン別の不良率分析)や時間帯別客数分析を行う飲食業や小売業等では、日時という列が重要な切り口となります。
今回のサンプルデータもシンプルですが、モデリングの部分でやっかいなところが2つあり、
- 日時でのモデリング
- 稼働時間を計算するするための変換が必要
を如何に効率よく実現できるかが重要になります。ここで、上記データを元に、Power BIのモデリングのベストプラクティスであるスタースキーマを作ることを考えていきます。
- ファクト(Fact)テーブル(Data)
- ディメンション(Dim)テーブル
- 日時列を「日付テーブル」(dCalendar)と「時刻テーブル」(dTime)という2つのDimテーブルに分解(※ここが重要)
- 学習ID(教育係)に関するDimテーブル(dPerson)
- 学習内容(項目)に関するDimテーブル(dSKU)
日付テーブルや時間テーブルはDAXを使って作っても良いですが、個人的にはPower Queryのほうが便利だと思うので、以下のように作っていきます。
//RawData(こちらのクエリは参照用)
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"nZo7jhxHDIavMthYwDbr0a9sjU0UCHDgwICg0LEFO3DuwIlyX0EnUOLzWBB8CxdZ0z0zQpFVP7NJfnzDKhaf/f79U5hCeJ7yc5guRHtc9ml6evP08vJKQX79+Osfv/x2+eFt+fnf35++fv709OENpPr25c9///nrURUvlPZEbdXLx4+/KzTR5cnUtXjTZd3jFff607s8D5uWk6Fqoyjuk6VSWLP9D1ssKmaFCUTRhYokwyg2K+KstAf0CIsq2/9QYeU9oqwgpxFAlqjiirOiizXvhLKixzUim5VRL6yPcsZZ856sf9hipWIWbSAqyUNeYBTfFho0kphl3XGLldks64pbqMxmEXpb2eWEmR9XRm9r9oTdmZ0wWe7URnHMcLAy/o4XfltoKFzECdHbWsQs9B0vLidcPU64usxaXdlkZbOC9fpbrI1vC00mG5uV0JCxcSSc0CPc+I4JvK3IJY2ZWRuoKCVNAv09Tp6QwaoER11WdbK4wioOD+b+SA6HLyKHZ0TyJJOrCvKM+Xki2DNEBL/jqkI9o6pKsWu9/hYrwFFXRBuYtkTE1R2OYseA8lZVOU4wSP8EOWFRcU1IUJ0mInYMqF0oKq7TgvX/Wiip0xIU36sKzSVVhTZcRSW1CaF2zXKE6DOeXXbViga1aynPxHSnFmpxXdfiMmvptoQt1lrMwlL/VQQHp1UcA4rvhworP6sq2EFNYZUUidolxROUIkW0nSMhgOQJhJvH3UlqJ/CyaPJkSMJrjEMEPixy5WKSqQlBlVNRcSaJUBsuIilyYZQjXpBMF9DET5y1IviwqE4XcJTnthLfVgLDIGVH5VREPAsCYxNl123V6QLqhKsjNtHqqdJoxYe6hypDIbeOgktSuN7xy+vPFGLqsI5h9WFXU6UPqyPOup0GwkpnpEFYtzptmCUz7oiypAcKAWfdXjLCuo0/h1l1Wm2dRndajbDorOER1q09RlgZ98OHyfgwK96FjWFUvC/hARSPQFA37Kt6k/FhVM1daNRILo/vqxRWiRpoNEz3U6RhVr4rC4dRIrLOXSMlxShj/SkT/45On/krf9Lmxduadpw3lwaAlJs2cCzT/N6kSePlwHGAU5zf5sWeTuHNZ78M8BZX0F/uy9Jhx1zuF7YIyxGxlvveDWF1VNpuQ8uAxpXxLMBxY9LXa6do4tiTleRp80g9kx6vo1PXN1pQtnlxj3ZgUHnZPhd1YURKWWHwZI+jJVODJzqtdNd5dVWiFdQ6r649cPsiFbee4AAmsmDHWWX3MfXiur5pgb2zbk0S/NijfKxCLl46J2sYL6stm8a77nci+BhEtvXerLLimeC3fug6t6DwqPeG1AWW1lDZvHhuRTBeUgs/m5fh13AszbTq1ODxBkzraQ1c7Rlx8+pmCioghlTaPitATWPdZ3XGK+o+C2oaz30WjoK7q3OfBXVXxz5LS6z2PgubNVVVsntNhTWrucpaMk29YKCutCKYUM/1D+r16/13acO2rZKmUP94WP8Ms+QzGC0KWEsZdAb0/QIIYc32y2wvgLhAB1n08PXMMMsxHzz2MlrLbu9lwDBVRHqGsNYyt6+IARR7POiFjysghIXOgL5bAQ2jksfh6zJHqzws1qyWf9ZeRk/n1l6G1P7XYmU4GD7ugIZZs1HUqqjZ4/CSKgOO4kSEOnz9iAP1wr6qxeKPWbUXqWdKWqwaQM+UJJ9/wCFKPmjVBiNDC8JhVJ3BjPj8h/8B",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [日時 = _t, 教育担当ID = _t, 学習内容 = _t, 開始終了 = _t]
),
ChangeDT = Table.TransformColumnTypes(
Source,
{{"日時", type datetime}, {"教育担当ID", type text}, {"学習内容", type text}, {"開始終了", type text}}
)
in
ChangeDT
Power BIやExcelで上記構文を空白のクエリ内にコピペすれば今回のサンプルデモのデータを作ることができます(下記ステップ参照)。
RawDataというクエリはステージングクエリとなり、それ自体はモデルに読み込まれないものの、他のクエリ(Dimテーブル)を作るための参照用クエリの役割を果たすことになります。
以下、⑥~⑪までと同じ要領で、それ以外のクエリを作っていきます。
//dPerson(教育係のDimテーブル)
let
Source = RawData[[教育担当ID]],
Distinct = Table.Distinct(Source),
Sorted = Table.Sort(Distinct, {{"教育担当ID", Order.Ascending}}),
AddSort = Table.AddIndexColumn(Sorted, "IDSort", 1, 1, Int64.Type),
AddTarget = Table.AddColumn(AddSort, "担当者", each {"Azureマニア", "Appsマニア", "PBIマニア"}{[IDSort] - 1}, type text),
AddCompensation = Table.AddColumn(AddTarget, "時間報酬", each {7000, 6000, 5000}{[IDSort] - 1}, Int64.Type)
in
AddCompensation
//dSKU(学習項目のDimテーブル)
let
Source = RawData[[学習内容]],
Distinct = Table.Distinct(Source),
SortColumn = Table.AddIndexColumn(Distinct, "学習内容Sort", 1, 1, Int64.Type)
in
SortColumn
- StartDate(パラメーター、モデルへ読み込まない)
//StartDate(dCalendar用のパラメータ、RawDataの一番古い日付を取得)
let
Source = Date.From(List.Min(RawData[日時]))
in
Source
- EndDate(パラメーター、モデルへ読み込まない)
//EndDate(dCalendar用のパラメータ、RawDataの一番新しい日付を取得)
let
Source = Date.From(List.Max(RawData[日時]))
in
Source
//dCalendar(StartDateとEndDateをパラメータとした日付テーブル)
let
Source = List.Dates(StartDate, Number.From(EndDate - StartDate) + 1, Duration.From(1)),
ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenameColumn = Table.RenameColumns(ConvertToTable, {{"Column1", "Date"}}),
DateToRecord = Table.AddColumn(RenameColumn, "GetData", each Date.ToRecord([Date])),
Expanded = Table.ExpandRecordColumn(
DateToRecord,
"GetData",
{"Year", "Month", "Day"},
{"Year", "Month", "Day"}
),
AddName = Table.AddColumn(
Expanded,
"AddName",
each [
YearName = Date.ToText([Date], "yyyy年"),
MonthName = Date.ToText([Date], "M月"),
DayName = Date.ToText([Date], "d日")
]
),
ExpandedName = Table.ExpandRecordColumn(
AddName,
"AddName",
{"YearName", "MonthName", "DayName"},
{"YearName", "MonthName", "DayName"}
),
ChangeDT = Table.TransformColumnTypes(
ExpandedName,
{
{"Date", type date},
{"Year", Int64.Type},
{"Month", Int64.Type},
{"Day", Int64.Type},
{"YearName", type text},
{"MonthName", type text},
{"DayName", type text}
}
)
in
ChangeDT
ここまでできると、下図のように各種クエリが完成するはずです。
残りはdTimeとDataの2つのクエリとなります。
//dTimeは日付テーブルのように、時刻をDimにしたテーブル
let
Source = List.Times(#time(0, 0, 0), 24 * 60, #duration(0, 0, 1, 0)),
ConvertToTable = Table.FromList(Source, each {_}, type table [Time = time]),
Hour = Table.AddColumn(ConvertToTable, "Hour", each Time.Hour([Time]), Int64.Type),
Minute = Table.AddColumn(Hour, "Minute", each Time.Minute([Time]), Int64.Type),
HourName = Table.AddColumn(Minute, "HourName", each Number.ToText([Hour], "0時")),
AMPM = Table.AddColumn(HourName, "AM/PM", each if [Hour] < 12 then "午前" else "午後", type text),
TimeKey = Table.AddColumn(AMPM, "TimeKey", each Time.ToText([Time], "HHmm"), type text),
Bin = Table.AddColumn(TimeKey, "BinMinute", each Number.IntegerDivide([Minute], 10) + 1, Int64.Type),
BinName = Table.AddColumn(
Bin,
"BinMinuteName",
each
if [BinMinute] = 1 then
"00-09"
else if [BinMinute] = 2 then
"10-19"
else if [BinMinute] = 3 then
"20-29"
else if [BinMinute] = 4 then
"30-39"
else if [BinMinute] = 5 then
"40-49"
else
"50-59",
type text
)
in
BinName
dTimeというクエリは必ず作る必要があり、時刻や時間をベースとした分析を行う場合、日付テーブル(dCalendar)と時刻テーブル(dTime)を別々にすることがベストプラクティスとなります。
理由は、日付+時刻ベースの日付テーブルを使った場合、1対多というリレーションシップであっても日付テーブル側のカーディナリティが非常に高くなってしまい、リレーションシップのコストが高まるだけでなく、Factテーブル側において日付+時刻列の圧縮効率が落ちてしまい、結果としてデータモデルのサイズ肥大化に繋がってしまうためです。
//Dataは稼働時間を算出するために変換が必要
let
Source = RawData,
InsertDate = Table.AddColumn(Source, "日付", each DateTime.Date([日時]), type date),
InserteTime = Table.AddColumn(InsertDate, "時刻", each DateTime.Time([日時]), type time),
SelectColumns = Table.SelectColumns(InserteTime, {"日付", "時刻", "教育担当ID", "学習内容", "開始終了"}),
AddTimeKey = Table.AddColumn(SelectColumns, "時刻Key", each Time.ToText([時刻], "HHmm"), type text),
Pivoted = Table.Pivot(AddTimeKey, List.Distinct(AddTimeKey[開始終了]), "開始終了", "時刻"),
FillDown = Table.FillDown(Pivoted, {"開始"}),
FillUp = Table.FillUp(FillDown, {"終了"}),
RemoveDuplicate = Table.Distinct(FillUp, {"日付", "教育担当ID", "学習内容", "開始"}),
OperatingTime = Table.AddColumn(RemoveDuplicate, "稼働時間", each [終了] - [開始], type duration)
//データモデルへの読み込み後、分単位に換算したい場合は1,440を乗じる、時間単位に換算したい場合は24を乗じる(現時点、〇日という単位になっている)
in
OperatingTime
最後のDataクエリですが、こちらは下図のように、元々は2行となっているものを1行に変換し、[開始] - [終了]で稼働時間を算出するために行う変換となります。
上記ソースコードで問題なく変換できると思いますが、ポイントは一回Pivotをしておくことであり、UI操作するならばステップ「Pivoted」は以下のように行います。
ここで重要なことは①でピボットする列を必ず選択し、その後の「値列」を時刻に設定、かつ、詳細設定オプションから「値の集計関数」を集計しないにすることです。これによって、今まで縦でしか見れなかった開始と終了を横でも見れるようにすることができます。一方で、Pivotをすると下図のようにnull値が出現するため、それぞれnullを埋める(フィルする)作業をやってあげます。
これにより、重複するレコード(行)が出現しますが、[日付]+[教育担当ID]+[学習内容]+[開始]というコンボで重複を削除することができ、[時刻Key]は開始時の時刻と同じになります。[時刻Key]はdTimeとのリレーションシップを作る上で必須の外部キー*1となります。
一見するとこのやり方は良さそうに見えますが、実はPower Queryの適用したステップを少しでも変更すると、Pivotを行う時に不意にエラー(Pivotしようとしている列に同じ項目が複数行あり、Pivot後に値として設定した列に異なる項目がある場合に発生するエラー*2)が発生したり、Power BIが勝手に行の並び替えを行って、そもそもPivotできているのか確認が難しいといった事態に遭遇します(下記2つの図)。
そこでもう一つの記述方法を下記に示しておきます。
//別のやり方で作るDataクエリは開始でフィルターして横に終了時刻を追加するやり方
let
Source = RawData,
Start = Table.ToColumns(Table.DemoteHeaders(Table.SelectRows(Source, each ([開始終了] = "開始")))),
End = Table.ToColumns(Table.DemoteHeaders(Table.SelectRows(Source, each ([開始終了] = "終了")))),
Combined = Start & {List.First(End)},
TableFromColumns = Table.FromColumns(Combined),
PH = Table.PromoteHeaders(TableFromColumns, [PromoteAllScalars = true]),
RenameEnd = Table.RenameColumns(PH, {List.Last(Table.ColumnNames(PH)), "終了"}),
AddDate = Table.AddColumn(RenameEnd, "日付", each Date.From([日時])),
RenameStart = Table.RenameColumns(AddDate, {{"日時", "開始"}}),
ChangeDT = Table.TransformColumnTypes(
RenameStart,
{{"日付", type date}, {"教育担当ID", type text}, {"学習内容", type text}, {"開始", type time}, {"終了", type time}}
),
AddTimeKey = Table.AddColumn(ChangeDT, "時刻Key", each Time.ToText([開始], "HHmm"), type text),
OperatingTime = Table.AddColumn(AddTimeKey, "稼働時間", each [終了] - [開始], type duration),
SelectColumns = Table.SelectColumns(OperatingTime, {"日付", "教育担当ID", "学習内容", "時刻Key", "開始", "終了", "稼働時間"})
//データモデルへの読み込み後、分単位に換算したい場合は1,440を乗じる、時間単位に換算したい場合は24を乗じる(現時点、〇日という単位になっている)
in
SelectColumns
このやり方は最初のほうでStartとEndの両方をリストのリストとして格納し、「終了」のほうにある列を「開始」のほうのクエリに追加するやり方となります。やや面倒ですが、こちらのほうが安心できるという人もいるかもしれませんので、好みに合わせて使うと良いでしょう。また、測ってはいませんが、後者のほうがデータ量が増えた際にもしかしたらクエリパフォーマンスが良いかもしれません(最初に紹介したやり方で重複削除をやっていますが、これはかなりメモリインテンシブな作業となります)。
最終的に仕上がったこれらのテーブルは以下ようなデータモデルとなります。
やはり前処理がやや面倒だったわけですが、本来であれば[開始]と[終了]が上下に存在するのではなく、オリジナルデータを[開始]と[終了]の2列で用意してあげるのがベストなアプローチとなります。
ちなみに、細かい部分での留意点として、稼働時間で計算された結果はtype numberやtype timeではなく、type durationにする必要があります。type numberにしてしまうと以下のように、列内の結果が全て空白となってしまいます。
DAXの構築
最も時間がかかるモデリングが終了しましたので、いよいよDAXで指標を定義していきます。今回、例えば以下のような指標を作ってみたいと思います。
- コーチング回数
- 合計稼働時間
- 1回あたり平均稼働時間
- 合計報酬額
- 平均稼働時間_学習内容ベース
- 平均稼働時間_担当者ベース
- 平均稼働時間_日付内容ベース
- 累積稼働時間_日
- 累積稼働時間_時刻
- 直近7日平均稼働時間_休み加味
- 直近7日平均稼働時間_単純
- 稼働時間構成
- DumpFilters
---------------------
-- Measure: [コーチング回数]
---------------------
MEASURE Data[コーチング回数] =
COUNTROWS( Data )
//コーチング回数
FormatString = "#,0"
--------------------
-- Measure: [合計稼働時間]
--------------------
MEASURE Data[合計稼働時間] =
SUM( Data[稼働時間] ) * 24
//24で乗じることで〇時間という稼働時間を算出(そのままの場合、〇日という単位になる)
FormatString = "#,0.00"
----------------------------
-- Measure: [平均稼働時間_学習内容ベース]
----------------------------
MEASURE Data[平均稼働時間_学習内容ベース] =
AVERAGEX( VALUES( dSKU[学習内容] ), [合計稼働時間] )
//学習内容に対する平均稼働時間
FormatString = "#,0.00"
---------------------------
-- Measure: [平均稼働時間_担当者ベース]
---------------------------
MEASURE Data[平均稼働時間_担当者ベース] =
AVERAGEX( VALUES( dPerson[担当者] ), [合計稼働時間] )
//担当者別平均稼働時間
FormatString = "#,0.00"
----------------------------
-- Measure: [平均稼働時間_日付内容ベース]
----------------------------
MEASURE Data[平均稼働時間_日付内容ベース] =
AVERAGEX(
SUMMARIZE(
Data,
dCalendar[Date],
dSKU[学習内容]
),
[合計稼働時間]
)
//学習内容別の1日当たりの平均稼働時間
FormatString = "#,0.00"
----------------------
-- Measure: [累積稼働時間_日]
----------------------
MEASURE Data[累積稼働時間_日] =
IF(
ISFILTERED( dCalendar ),
CALCULATE (
[合計稼働時間],
dCalendar[Date] <= MAX ( dCalendar[Date] ),
REMOVEFILTERS ( dCalendar )
)
)
//日付ベースの累積(日付で見た場合は累積、時刻(HourName)で見た場合は単純合計)
FormatString = "#,0.00"
-----------------------------
-- Measure: [直近7日平均稼働時間_休み加味]
-----------------------------
MEASURE Data[直近7日平均稼働時間_休み加味] =
var _days = 7
var _current = MAX( dCalendar[Date] )
var _period = DATESBETWEEN( dCalendar[Date], _current + 1 - _days, _current )
var _result = AVERAGEX( _period, [合計稼働時間] )
return _result
//稼働時間がゼロの日はカウントせず、平均を算出(例:6日しか稼働していなかった場合、6で除算)
FormatString = "#,0.00"
---------------------------
-- Measure: [直近7日平均稼働時間_単純]
---------------------------
MEASURE Data[直近7日平均稼働時間_単純] =
var _days = 7 - 1
var _current = MAX( dCalendar[Date] )
var _period = DATESBETWEEN( dCalendar[Date], _current - _days, _current )
var _count = COUNTROWS( _period )
var _result = DIVIDE( CALCULATE( [合計稼働時間], _period ), _count )
return _result
//稼働時間がゼロの日に関係なく、7で除算した平均
--------------------
-- Measure: [稼働時間構成]
--------------------
MEASURE Data[稼働時間構成] = DIVIDE( [合計稼働時間], CALCULATE( [合計稼働時間], ALLSELECTED( Data ) ) )
FormatString = "0.0%;-0.0%;0.0%"
-------------------------
-- Measure: [DumpFilters]
-------------------------
MEASURE Data[DumpFilters] =
//https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/
VAR MaxFilters = 3
RETURN
IF (
ISFILTERED ( Data[稼働時間] ),
VAR ___f = FILTERS ( Data[稼働時間] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, Data[稼働時間] )
VAR ___d = CONCATENATEX ( ___t, Data[稼働時間], ", " )
VAR ___x = "Data[稼働時間] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( Data[開始] ),
VAR ___f = FILTERS ( Data[開始] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, Data[開始] )
VAR ___d = CONCATENATEX ( ___t, Data[開始], ", " )
VAR ___x = "Data[開始] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( Data[学習内容] ),
VAR ___f = FILTERS ( Data[学習内容] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, Data[学習内容] )
VAR ___d = CONCATENATEX ( ___t, Data[学習内容], ", " )
VAR ___x = "Data[学習内容] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( Data[教育担当ID] ),
VAR ___f = FILTERS ( Data[教育担当ID] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, Data[教育担当ID] )
VAR ___d = CONCATENATEX ( ___t, Data[教育担当ID], ", " )
VAR ___x = "Data[教育担当ID] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( Data[時刻Key] ),
VAR ___f = FILTERS ( Data[時刻Key] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, Data[時刻Key] )
VAR ___d = CONCATENATEX ( ___t, Data[時刻Key], ", " )
VAR ___x = "Data[時刻Key] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( Data[終了] ),
VAR ___f = FILTERS ( Data[終了] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, Data[終了] )
VAR ___d = CONCATENATEX ( ___t, Data[終了], ", " )
VAR ___x = "Data[終了] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( Data[日付] ),
VAR ___f = FILTERS ( Data[日付] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, Data[日付] )
VAR ___d = CONCATENATEX ( ___t, Data[日付], ", " )
VAR ___x = "Data[日付] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dCalendar[Date] ),
VAR ___f = FILTERS ( dCalendar[Date] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dCalendar[Date] )
VAR ___d = CONCATENATEX ( ___t, dCalendar[Date], ", " )
VAR ___x = "dCalendar[Date] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dCalendar[Day] ),
VAR ___f = FILTERS ( dCalendar[Day] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dCalendar[Day] )
VAR ___d = CONCATENATEX ( ___t, dCalendar[Day], ", " )
VAR ___x = "dCalendar[Day] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dCalendar[DayName] ),
VAR ___f = FILTERS ( dCalendar[DayName] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dCalendar[DayName] )
VAR ___d = CONCATENATEX ( ___t, dCalendar[DayName], ", " )
VAR ___x = "dCalendar[DayName] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dCalendar[Month] ),
VAR ___f = FILTERS ( dCalendar[Month] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dCalendar[Month] )
VAR ___d = CONCATENATEX ( ___t, dCalendar[Month], ", " )
VAR ___x = "dCalendar[Month] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dCalendar[MonthName] ),
VAR ___f = FILTERS ( dCalendar[MonthName] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dCalendar[MonthName] )
VAR ___d = CONCATENATEX ( ___t, dCalendar[MonthName], ", " )
VAR ___x = "dCalendar[MonthName] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dCalendar[Year] ),
VAR ___f = FILTERS ( dCalendar[Year] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dCalendar[Year] )
VAR ___d = CONCATENATEX ( ___t, dCalendar[Year], ", " )
VAR ___x = "dCalendar[Year] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dCalendar[YearName] ),
VAR ___f = FILTERS ( dCalendar[YearName] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dCalendar[YearName] )
VAR ___d = CONCATENATEX ( ___t, dCalendar[YearName], ", " )
VAR ___x = "dCalendar[YearName] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dPerson[IDSort] ),
VAR ___f = FILTERS ( dPerson[IDSort] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dPerson[IDSort] )
VAR ___d = CONCATENATEX ( ___t, dPerson[IDSort], ", " )
VAR ___x = "dPerson[IDSort] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dPerson[教育担当ID] ),
VAR ___f = FILTERS ( dPerson[教育担当ID] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dPerson[教育担当ID] )
VAR ___d = CONCATENATEX ( ___t, dPerson[教育担当ID], ", " )
VAR ___x = "dPerson[教育担当ID] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dPerson[担当者] ),
VAR ___f = FILTERS ( dPerson[担当者] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dPerson[担当者] )
VAR ___d = CONCATENATEX ( ___t, dPerson[担当者], ", " )
VAR ___x = "dPerson[担当者] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dSKU[学習内容] ),
VAR ___f = FILTERS ( dSKU[学習内容] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dSKU[学習内容] )
VAR ___d = CONCATENATEX ( ___t, dSKU[学習内容], ", " )
VAR ___x = "dSKU[学習内容] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dSKU[学習内容Sort] ),
VAR ___f = FILTERS ( dSKU[学習内容Sort] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dSKU[学習内容Sort] )
VAR ___d = CONCATENATEX ( ___t, dSKU[学習内容Sort], ", " )
VAR ___x = "dSKU[学習内容Sort] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[AM/PM] ),
VAR ___f = FILTERS ( dTime[AM/PM] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[AM/PM] )
VAR ___d = CONCATENATEX ( ___t, dTime[AM/PM], ", " )
VAR ___x = "dTime[AM/PM] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[BinMinute] ),
VAR ___f = FILTERS ( dTime[BinMinute] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[BinMinute] )
VAR ___d = CONCATENATEX ( ___t, dTime[BinMinute], ", " )
VAR ___x = "dTime[BinMinute] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[BinMinuteName] ),
VAR ___f = FILTERS ( dTime[BinMinuteName] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[BinMinuteName] )
VAR ___d = CONCATENATEX ( ___t, dTime[BinMinuteName], ", " )
VAR ___x = "dTime[BinMinuteName] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[Hour] ),
VAR ___f = FILTERS ( dTime[Hour] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[Hour] )
VAR ___d = CONCATENATEX ( ___t, dTime[Hour], ", " )
VAR ___x = "dTime[Hour] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[HourName] ),
VAR ___f = FILTERS ( dTime[HourName] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[HourName] )
VAR ___d = CONCATENATEX ( ___t, dTime[HourName], ", " )
VAR ___x = "dTime[HourName] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[Minute] ),
VAR ___f = FILTERS ( dTime[Minute] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[Minute] )
VAR ___d = CONCATENATEX ( ___t, dTime[Minute], ", " )
VAR ___x = "dTime[Minute] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[Time] ),
VAR ___f = FILTERS ( dTime[Time] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[Time] )
VAR ___d = CONCATENATEX ( ___t, dTime[Time], ", " )
VAR ___x = "dTime[Time] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( dTime[TimeKey] ),
VAR ___f = FILTERS ( dTime[TimeKey] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, dTime[TimeKey] )
VAR ___d = CONCATENATEX ( ___t, dTime[TimeKey], ", " )
VAR ___x = "dTime[TimeKey] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
-------------------------
-- Measure: [1回あたり平均稼働時間]
-------------------------
MEASURE Data[1回あたり平均稼働時間] = DIVIDE( [合計稼働時間], [コーチング回数] )
FormatString = "#,0.00"
-------------------
-- Measure: [合計報酬額]
-------------------
MEASURE Data[合計報酬額] =
SUMX( VALUES( dPerson[時間報酬] ), dPerson[時間報酬] * [合計稼働時間] )
//合計報酬額2 = SUMX( Data, Data[稼働時間] * 24 * RELATED( dPerson[時間報酬] ) )と同じ結果
FormatString = "#,0"
-----------------------
-- Measure: [累積稼働時間_時刻]
-----------------------
MEASURE Data[累積稼働時間_時刻] =
IF(
ISFILTERED( dTime ) && NOT ISBLANK( [合計稼働時間] ),
CALCULATE(
[合計稼働時間],
dTime[Time] <= MAX( dTime[Time] )
)
)
//時刻ベースの累積(日付でフィルターすると1日おきにリセットされる)
FormatString = "#,0.00"
上記指標はTabular Editor 3(TE3)があれば、まだメジャーが何も入っていないモデルからTE3を外部ツールから立ち上げ、DAX Scriptに張り付けて同期を簡単に行うことができます。なお、全ての指標に関する説明はコメントにある通りとなります。いくつかのメジャーではDAXを書くのにvar(変数)を沢山使用していますが、少し複雑なメジャーを作る場合、変数を活用するのがパフォーマンス的にも可読性から見ても最適となります。
※TE2とTE3の違い
なお、DumpFiltersというメジャーですが、下図のように、どのような切り口でフィルターされているかを把握できるメジャーとなります。
こちらはDAX Studioで以下のステップに従って使えば簡単に作ることができます。
- Power BI Deskto > 外部ツール > DAX Studio
- 右クリック > Define Fitler Dump Measure (All tables)
- 出力されたDAX式をコピー
- Power BI Desktopで以下のようにメジャー名を変更
これでこのメジャーをテーブルビジュアルの中に入れれば、フィルター条件を確認できるようになります。
留意点
メジャー構築を含め、モデリングが完了しましたので、あとは適宜ビジュアルを作って傾向把握等を行っていきます。詳細は割愛しますが、最後に留意点として平均値(Average関数)の考え方についておさらいしておきます。
ずばり結論から言いますと、Average関数はシンプルである一方、気を付けないと混乱してしまう関数であり、Averageを使って計算する場合、常に何に対する平均か?というのを意識してDAXを記述していかないと、数字の意味が分からなくなってしまいます。
例えば、下図のように、平均稼働時間が3種類ある中で、全てが異なる数字を表示しているところに注目したいところです。左から見ていきますと、[平均稼働時間_学習内容ベース]では学習内容(Power Apps, Power BI)に対して平均値を算出しており、2つあるテーブルの2番目のテーブルでは[合計稼働時間]メジャーと同じ数値(25.50 vs 25.50、187.82 vs 187.82、ただし合計を除く)となっていますが、1番目のテーブルでは[合計稼働時間] vs [平均稼働時間_学習内容ベース](86.67 vs 42.83、0.15 vs 0.08、127.50 vs 127.50)ではPBIマニアを除く2つの担当者が異なる数値となっています。
カラクリは+ボタンを広げると分かりますが、数字が異なる担当者は2つの学習内容が含まれており、[平均稼働時間_学習内容ベース]というメジャーは合計値を2で割った数字(=担当者が行っている学習内容別の平均値)となっているわけです。なお、2番目のテーブルの合計(本当は平均値という名称になるべきだが)はPower AppsとPower BIの2つが含まれているため、数字は213.32÷2=106.66という平均値になっています。
同じ考え方でいけば、[平均稼働時間_担当者ベース]は担当者に対して平均値を算出しているため、1番目のテーブルでは合計(=平均)以外で全て同じ(担当者=1人のため、合計稼働時間は1を除算 = 合計稼働時間と同じ)となり、下にある2番目のテーブルの学習内容では「合計稼働時間」の値は学習内容の中で見えない担当者の数で除算して算出されています(下図)。
このように、DAX式を使ってメジャーを定義するときに、見えている部分だけでなく、見えていない部分もフィルター(=正確な表現で言えればIterate)して、平均値のみならず、使用した関数で結果を算出することができるのです。
この概念を理解していれば、最後の「平均稼働時間_日付内容ベース」というメジャーの解読が楽になりますが、こちらのメジャーの定義が以下の通り、
平均稼働時間_日付内容ベース =
AVERAGEX(
SUMMARIZE(
Data,
dCalendar[Date],
dSKU[学習内容]
),
[合計稼働時間]
)
日付(dCalendar[Date])×学習内容(dSKU[学習内容])のコンビネーションで形成されるテーブルに対して平均値を求めることになりますので、ビジュアル(テーブル)内にて隠されているテーブル(dCalendarのDate列=日数)を除算してあげる必要があります(下図)。
平均値は特に間違い&混乱しやすいものであり、DAXのこの概念についてしっかり理解しておく必要があります。長くなりましたが、これにて今回は終了となります。
>>ダウンロード(Raw Data)
>>ダウンロード(完成版)