スライサで選択したものとそれ以外でそれぞれ集計するにはどうするかっていう話が、先般開催した PBIJP DAX Boot Camp #2 でありまして、メジャーだけで何とかするよりモデリングから考慮するとよいかもしれないねという話をしたのです。
DAX難しいですか？何度も言いますけど、勉強しなければできるようになりませんから、安心して勉強してください📢 https://t.co/wlKqXjYekp #PBIJP
— Takeshi Kagata / Power Platform 部 Power BI 科 🛏💤 (@PowerBIxyz) September 9, 2020
こういうこと
スライサ ビジュアルで選択された商品の"区分名"のうち、選択されたものとそれ以外でそれぞれ"受注額"を集計するみたいな。
データモデルはシンプル。
どうするか
ビジュアルをサポートするテーブルが必要
例にあるビジュアルに限らずで、見出し / 軸として投影する値を持つ列が含まれるテーブルが存在しなけれればならないから用意する。
Selected_Others =
DATATABLE(
"ID", INTEGER,
"Index", STRING,
{
{ 1, "Selected" },
{ 2, "Others" }
}
)
| ID | Index |
|---|---|
| 1 | Selected |
| 2 | Others |
計算テーブル / Calculated table で用意した。
こんな感じのテーブルをこさえることができるのです。メジャーを記述するときの経過を表示しながらなのでテーブル ビジュアルががちょうどよいというだけ。
すべての "区分名" とは
スライサ ビジュアルの選択に関わらないすべての Products[区分名]
Selected / Others =
VAR AllCategories = ALL( Products[区分名] )
RETURN
CONCATENATEX(
AllCategories,
Products[区分名], "|"
)
まぁこうなるよね。ALL ですから。すべての行でおなじ結果を得ているのはリレーションシップによる評価コンテキストがないからだし。
選択された "区分名" とは
Selected / Others =
VAR AllCategories = ALL( Products[区分名] )
VAR SelectedCategories = ALLSELECTED( Products[区分名] )
RETURN
CONCATENATEX(
SelectedCategories,
Products[区分名], "|"
)
ALLSELECTED 関数の効果
ALLSELECTED 関数の使い方としてはよくあるもので、ALLSELECTED 関数で スライサ ビジュアルで選択済みの Products[区分名] を参照することができた(ようにみえる)。
テーブル ビジュアルで ALLSELECTED 関数が含まれる該当のメジャーが評価されるとき、外部にあるビジュアル (ここでいうとスライサ ビジュアル) の結果を取得しているものではなく、たまたまそうなっているという認識が必要。
ビジュアルの相互作用で スライサ ビジュアル の選択した状況は テーブル ビジュアル に伝搬しているだけであって、ALLSELECTED 関数が スライサ ビジュアルの結果をスキャンしているわけではない。たまたまそうなるっていうだけ。
ここでは VALUES 関数でも同じ結果を得ることができる。
Selected / Others =
VAR AllCategories = ALL( Products[区分名] )
VAR SelectedCategories = VALUES( Products[区分名] ) // 🤪ココ
RETURN
CONCATENATEX(
SelectedCategories,
Products[区分名], "|"
)
選択されていない "区分名" とは
すべての Products[区分名] から 選択された Products[区分名] を除外すればよい。EXCEPT 関数を使った。
Selected / Others =
VAR AllCategories = ALL( Products[区分名] )
VAR SelectedCategories = ALLSELECTED( Products[区分名] )
VAR UnselectedCategories = EXCEPT( AllCategories, SelectedCategories )
RETURN
CONCATENATEX(
UnselectedCategories,
Products[区分名], "|"
)
行ごとの投影を実現する
そもそもリレーションシップがないから フィルタ コンテキストを用意してくれない。なので自力で実装する。イメージとしては、テーブルの行ごとの 'Selected_Others'[Index] を検証しながら結果を得るしかない。
Selected / Others =
VAR AllCategories = ALL( Products[区分名] )
VAR SelectedCategories = ALLSELECTED( Products[区分名] )
VAR UnselectedCategories = EXCEPT( AllCategories, SelectedCategories )
VAR CurrentIndex = SELECTEDVALUE( 'Selected_Others'[Index] )
RETURN
CurrentIndex
SELECTEDVALUE 関数は IF( HASONEVALUE( <column_name> ), VALUES( <column_name> ) ) と等価。合計列では、'Selected_Others'[Index] は単一の値を持たないので blank
SWITCH 関数で行ごとの投影を切り替えを実装
Selected / Others =
VAR AllCategories = ALL( Products[区分名] )
VAR SelectedCategories = ALLSELECTED( Products[区分名] )
VAR UnselectedCategories = EXCEPT( AllCategories, SelectedCategories )
VAR CurrentIndex = SELECTEDVALUE( 'Selected_Others'[Index] )
RETURN
SWITCH(
CurrentIndex,
"Selected",
CONCATENATEX( SelectedCategories, Products[区分名], "|" ),
"Others",
CONCATENATEX( UnselectedCategories, Products[区分名], "|" ),
CONCATENATEX( AllCategories, Products[区分名], "|" )
)
あとはお好きなように。
Selected / Others =
VAR AllCategories = ALL( Products[区分名] )
VAR SelectedCategories = ALLSELECTED( Products[区分名] )
VAR UnselectedCategories = EXCEPT( AllCategories, SelectedCategories )
VAR CurrentIndex = SELECTEDVALUE( 'Selected_Others'[Index] )
RETURN
SWITCH(
CurrentIndex,
"Selected",
CALCULATE( [受注額], SelectedCategories ),
"Others",
CALCULATE( [受注額], UnselectedCategories ),
CALCULATE( [受注額], AllCategories )
)
思ったこと🙄
レポートの機能を重視するときになって初めて検討する手段ではあるから、常にパフォーマンスについての検証が重要。
レポート レベル フィルタ / ページ レベル フィルタ が関わると期待する動作をしないことがありますよ。なんで？と思ったら、評価コンテキストについてよーく勉強したらいい。