エクセル、パワーピボットで集計基準にしているリレーションシップを切り替える方法について試してみました。
一つのテーブルに複数の日付列があるので、その列基準のリレーションシップでそれぞれの集計をする。
テーブルとリレーションシップ
- 使用するテーブルは以下です。
- ディメンション：日付のテーブル
- ファクト：作業履歴のテーブル
- 同じテーブルに対して、複数のリレーションシップをつける。
表示したいピボット
行ラベル：t作業[作業エリア]
列ラベル：Calendar[Date]
値の所には、この行列に当てはまる作業分類を表示したい。
その時に、開始日列、完了日列を使い分ける必要がある。
DAX式
リレーションの状態
開始日列：アクティブ
完了日列：非アクティブ
文字列を表示するために、CONCATENATEXとVALUESを使います。
この場合は、行・列に当てはまるt作業'[作業分類]列で、重複は削除され、複数の場合は、カンマ区切りになります。
fx開始
=CONCATENATEX(VALUES('t作業'[作業分類]),'t作業'[作業分類],",")
fx完了
=CALCULATE(CONCATENATEX(VALUES('t作業'[作業分類]),'t作業'[作業分類],",")
,USERELATIONSHIP('t作業'[完了日],'Calendar'[Date]))
USERELATIONSHIPの使い方
USERELATIONSHIPはとりあえず、CALCULATE関数のフィルター部分で指定するという方法なら使えそうです。
詳しくは以下リンク先と、DAXでの基本、CALCULATE関数について勉強しなおさないとですね。
つくったDAXで表示
フィールドの設定
開始日・完了日によって、リレーションの日別で集計できてる！
開始日・完了日をフィルターで切り替える
- パワークエリ編集から、新しく「空のクエリ」をつくる
- 以下を入れてテーブルをつくる
- このテーブルはリレーションしないでそのまま
t区分
let
ソース = #table({"区分"}, {{"開始"}, {"完了"}}),
変更された型 = Table.TransformColumnTypes(ソース,{{"区分", type text}})
in
変更された型
切り替え用DAX
fx区分
=IF(
COUNTROWS(VALUES('t区分'[区分])) = 1,
SWITCH(
VALUES('t区分'[区分]),
"開始", 't作業'[fx開始],
"完了", 't作業'[fx完了]
)
)
フィールドの設定
フィルターで切り替えられた！
リレーションシップの切り替えを使わない方法
そもそも非アクティブなリレーションをつくって、切り替えるという事をしなくていいようにできないかやってみました。
パワークエリで編集
日付列が二つあるので、難しくなってしまうのでこれを一つにします。
- パワークエリエディターで、t作業の「開始日」「完了日」を選択して、変換-列のピボット解除
- 以下のようになります
- 列名はいい感じに変えておく 属性⇒処理分類 値⇒日付
エディター
let
ソース = Excel.CurrentWorkbook(){[Name="t作業"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"作業分類", type text}, {"開始日", type datetime}, {"完了日", type datetime}, {"作業エリア", type text}}),
ピボット解除された列 = Table.UnpivotOtherColumns(変更された型, {"作業エリア", "作業分類"}, "属性", "値"),
#"名前が変更された列 " = Table.RenameColumns(ピボット解除された列,{{"属性", "処理分類"}, {"値", "日付"}})
in
#"名前が変更された列 "
リレーションシップとDAX
- t作業[日付]とCalendar[Date]でリレーションシップをつくる
- t作業テーブルに日付の列は一つだけなので、非アクティブなリレーションシップは必要なくなりました
- DAXをつくる
fx開始
=CALCULATE(CONCATENATEX(VALUES('t作業'[作業分類]),'t作業'[作業分類],","),
't作業'[処理分類]="開始日")
fx完了
=CALCULATE(CONCATENATEX(VALUES('t作業'[作業分類]),'t作業'[作業分類],","),
't作業'[処理分類]="完了日")
fx区分
=IF(
COUNTROWS(VALUES('t区分'[区分])) = 1,
SWITCH(
VALUES('t区分'[区分]),
"開始", 't作業'[fx開始],
"完了", 't作業'[fx完了]
)
)
完成したピボットテーブル
最終的には、リレーションシップを切り替える場合と同じようにできました！
どの方法がいいかは、その時々で検討する必要がありますね。
-
開始・完了を並べる場合
-
フィルターで切り替える場合
