よく使うDAX式の解説【PowerBI】【Excel】【メジャー】
Вставка
- Опубліковано 8 сер 2024
- ▼Udemy講座『Power BI Desktop超入門講座～データベースなどの知識０からでも、業務で使えるようになる！～』↓↓↓
www.udemy.com/course/power-bi...
▼設定ファイルリンク
drive.google.com/drive/folder...
▼PowerBIダウンロードリンク(無料)
www.microsoft.com/ja-JP/downl...
▼Twitter :
/ it_toolgakusyu
【目次（クリックで飛べます）】
00:00 概要
00:45 扱うデータについて
01:10 データ参照設定
01:57 テーブル作成(準備)
02:36 スライサーの作成
03:11 表の作成
03:35 SUM
05:20 SUMXとFILTER
09:05 式の改行と字下げ
10:06 SUMXとFILTER関数の詳細
12:56 ALL
13:53 DIVIDE
16:50 ALLSELECTED
18:26 COUNT
20:01 CALCULATE
24:02 VAR , RETURN , IF , ISBLANK , BLANK - Навчання та стиль
ご質問お待ちしています！
【目次（クリックで飛べます）】
00:00 概要
00:45 扱うデータについて
01:10 データ参照設定
01:57 テーブル作成(準備)
02:36 スライサーの作成
03:11 表の作成
03:35 SUM
05:20 SUMXとFILTER
09:05 式の改行と字下げ
10:06 SUMXとFILTER関数の詳細
12:56 ALL
13:53 DIVIDE
16:50 ALLSELECTED
18:26 COUNT
20:01 CALCULATE
24:02 VAR,RETURN,IF,ISBLANK,BLANK
勉強になりました。有用な情報を発信して頂き感謝します。
ご視聴ありがとうございます！お役に立てて嬉しいです！
Udemyでも学んでいます。ありがとうございます。
Udemyの講座もご受講いただきありがとうございます！
とても分かり易く解説いただきありがとうございます。動画の内容で一つ教えてください。
動画の最後の「利益率をグラフで線形表示させるため、値がないところを０と設定する」DAXのところで、以下のように一旦ISBLANK(ngsuu)&&(gokei), BLANK()を挟んでいるのはなぜなのでしょうか？（無くてもよいのでは？と思った次第です） 素人質問かもしれませんが、ご教示いただけると幸いです
IF(ISBLANK(ngsuu)&&(gokei),
BLANK(),
if(ISBLANK(ngsuu),
0,
DIVIDE(ngsuu,gokei)
))
コメントありがとうございます！データがない所を表示させたくないため、このような形にしています。例えばマトリクスで縦軸：日付、横軸：機種、値：NG率とした場合に、最初の条件式がないと、データがない月も0%と表示されてしまいます。この仕様でも問題なければ、省いてしまっても大丈夫だと思います。
@@IT-ToolGakusyu 返信いただきありがとうございました。よくわかりました。
これからもよろしくお願いします。
特にDAXについては動画で見せて頂くのが分かりやすいので、続編が出るのであれば楽しみです。
動画を見てDAX勉強させて頂いてます。
ありがとうございます。
DAXで、エクセルでいうSumif とアスタリスク組み合わせた、部分一致している項目を合計した式は作れますでしょうか？
例えば動画の中でsumx とフィルターを組み合わせた式で、「製品」という文字列をのものを全て合計するような式が作る方法が存在しますでしょうか。
教えて頂けるとありがたいです。
よろしくお願いします。
式に不備がありましたので修正して再度アップしました。失礼しました（2021/10/3 22:00修正）
-----------------------------------------
下記の式ようにCONTAINSSTRING関数を使えばできそうです。
sum_test =
CALCULATE (
SUM (sample_table[value] ),
FILTER (
ALL (sample_table[category] ),
CONTAINSSTRING (sample_table[category], "test" )
)
)
設定ファイルを下記リンクに共有しますのでご参照ください。
drive.google.com/drive/folders/1rKd5oQKCp2oyaLaOn23tGg605_hJPyYb?usp=sharing
大変参考になる動画でした。ありがとうございます。今困っていることについて質問したいのですが、例えば
名前 個数
--------------
----------
リンゴ 5
みかん 3
梨 2
パイン 8
いちご 12
----------------------
のような表を棒グラフにするとします。そして、
--------
-------
□パイン
□いちご
--------
-------
のような「パイン、いちご」のみのスライサーを制作して「リンゴ・みかん・梨」は固定したまま「パイン、いちご」のみをスライサーの選択によって
表示・非表示にしたいです。どんな手法でも構いませんので何か方法はございますでしょうか？
コメントありがとうございます！グループ化を使って簡単に作成してみました。このような可視化でも問題ないでしょうか。下記リンクをご確認いただけますか。drive.google.com/drive/folders/19Nmi6AeoBXrPPhOnOS5Ln9IM17JuOO1U?usp=sharing
@@IT-ToolGakusyu 素早い回答ありがとうございます。グループという方法があるのですね。今まで使用したことがなかったので大変参考になりました。理想を言わせていただくと、「みかん および りんご および なし」をスライサーで操作できなくする、常時アクティブにすることが理想です。そのような方法はやはり存在しないのでしょうか？
残念ながら難しいと思います。スライサーを使う場合はそれ以外（パイン、いちご以外）の項目もどうしても入ってしまいます。よろしければ上記のような仕様にしたい理由があれば、教えていただけると何かご教示できるかもしれません。
いつもありがとうございます。比率についての質問です。例えば月ごとの売上等で管理されている数表において、月ごとの売上割合を算出するDAX式はどの様になりますか？動画の方法でやってしまうと表示されている全ての総計に対する割合となってしまったため教えて頂きたいです。値の表示方法でも可能なのですが桁数などを好みに合わせたかったので。。。よろしくお願いします。
返信が遅くなってしまいすみません。
下記のようにALLSELECTEDの中にカテゴリの項目を入れるとできると思います。
6_売上割合 =
DIVIDE(
SUM('テーブル1'[売上]),
CALCULATE(SUM('テーブル1'[売上]), ALLSELECTED('テーブル1'[国],'テーブル1'[製品名]))
)
サンプルで作ってみましたので下記をご確認ください。
drive.google.com/drive/folders/14gIjkJwEZkI5gLhUreoB4S0FrWlFuTsX?usp=sharing
@@IT-ToolGakusyu
分かり易い解説ありがとうございます。これからもよろしくお願いします。
いつもありがとうございます。教えてください。Excelなど良く使う、上行の値を次の行に単純に加算していくような方法。例えば、日次での変遷する残高をその日の行の＋ーで加減算を行って、その日の残高を作る。Excelでは簡単に上位の残高＋ーその行の数値により日々の確定残として定義できますが、BIの場合、どのようにすればいいのでしょうか？
コメントありがとうございます！こちら開設動画作成しますのでしばらくお待ち下さい！
PowerBIの設定ファイルを作成してみましたのでご確認いただけますか。合っていれば解説動画作成します。drive.google.com/file/d/1kVWGf50VfQt8N_yjZknAGHxXzOYLich4/view?usp=sharing
@@IT-ToolGakusyu さん、いつもありがとうございます！はい！この「累計」カラム、そのものです。唯一、入出金のフィールドは合算ではなく、貸方、借方など＋ー複数に別れています。
ほぼExcelを利用するのは、この為だ！ぐらいの一般的な受払表だとは思っています。
プラスとマイナスで、それぞれ列が分かれてる感じですね！1つの列に結合できるので、その方法も簡易的な動画で解説しますね！
@@IT-ToolGakusyu さん、はい、ご理解の通りです。よろしくお願いいたします。また、既に別な質問があるのですが、こちらを頂いてから、また、投稿させて頂きます。
いつも参考にさせていただいてます。
機種ごとの最小値を算出する式についてご教授ください。
例：設定ファイル（data.xlsx）のG列に追加で、項目名「サイズ」として、それぞれの行に100や50や10や200といった数値が入っているとします。
そして、隣のH列に、機種ごとの最小値を表示したいと思っています。
EXCELでいうところの、H列の式は「=MINIFS(G:G,E:E,[@機種])」になります。
POWER BIのデータビューで列を挿入して、上記を算出する式を記載したいのですが…おわかりになられますでしょうか？
お手数おかけしますがよろしくお願い致します。
コメントいただきありがとうございます！他にも方法があるかもしれませんが、DAXでメジャーを作成し、その後DAXでテーブルを作成する方法で一応所望のテーブルを作成することができます。下記リンクから設定ファイルをダウンロードできますのでご確認いただけますか。よろしくお願いします！drive.google.com/drive/folders/1_ztn-y4wrIhQCFSfeTXKU4V4ZUN4l8-4?usp=sharing
@@IT-ToolGakusyu
早速のご確認ありがとうございます。
これです！
CALCULATE(MIN('テーブル1'[サイズ]),FILTER(ALL('テーブル1'),'テーブル1'[機種]=CurrentKisyu))
この、CALCULATEとMINの組み合わせがうまく書けてませんでした。
大変助かります。
どーもEXCELの癖で、下にコピーする式のイメージをしてしまうので、、、精進が必要です。
ご対応いただきありがとうございました。またご質問させていただくこともあるかと思いますが、よろしくお願い致します！
今後も動画参考にさせていただきます。
お役に立てて良かったです！これからもよろしくお願いします！
@@IT-ToolGakusyu
度々申し訳ございません。追加でひとつご教授ください。
新しいメジャーを作成して、NewTableを作るまではうまくできました。
ここで最終的行いたいことが、最初の「テーブル1」の中にひとつの列として、「機種毎のサイズの最小値」を追加したいと思っています。
作成頂いた設定ファイル（サンプル）sample.pbix内。
テーブル1の中に既にある「機種ごと最小」という列は本来は無いものかと思います。
（取り込み元データのEXCEL内で、例として作成した関数値がPOWER BIに取り込まれたものだと思います）
自分で「テーブル1」の中に新しい列の追加をして、単にNewTableの最小とイコールしても、循環参照的なことを言われエラーになってしまいます。
また、EXCELのVlookup的なイメージで、新しくできたNewtableから値を引っ張ってこようとLOOKUPVALUE？あたりを
試してみましたが、こちらも循環参照的なことを言われてしまいます。
テーブル1内に新規の列としてDAXで書いた「機種毎のサイズの最小値」を追加することは可能でしょうか？
お手数おかけします…ご確認いただけますと幸いです。
テーブル１内に新規の列としてメジャーを追加することは難しいと思います。。テーブル１の代わりにNewTableのデータを可視化に使うということはできないのでしょうか。他にも必要な列があれば、NewTable作成に使っているSummarize関数の引数の数を増やして列を増やすことが可能です。
ご教授ください。
同じカラム列の数値が連続5回上昇していたら折れ線グラフにした際、5回分の値に色付けしたいのですがやり方分かりますでしょうか?
コメントありがとうございます。
下記リンクのサイトを見るとできそうです。メジャーの作成がポイントだと思いますので少し検討してみますね。
qiita.com/yugoes1021/items/8c94c148b391758b4ce5
バイブルのように何度も見ながら勉強しています。
動画とは直接関係ないのですが、少し困っていることがあってここで質問させていただきます。
カードに値を表示します。このとき、月を選択するスライサーを作成してあって、それで月を選択したときと全て選択しないときとで、カードに表示するDAXメジャーを切り替えたいと思っているのですが、そういうことは可能なのでしょうか。IF文でできそうかなと思ったのですが、スライサーの選択、非選択の書き方が分かりません。最終的にはそもそもそんなことできなさそうにも感じたので、もし何か方法があればご教示いただきたく、よろしくお願いします。
いつもご視聴ありがとうございます。
カードの値の切り替えですが、hasonevalueという関数を使えば、一意の値にフィルターされているという条件分岐が可能です。サンプルファイルを下記リンクに置きましたのでご確認ください。
drive.google.com/drive/folders/1l2DvO7k41vKuDMjTmdloe7jIcUx_3IZ7?usp=sharing
@@IT-ToolGakusyu さん、早速の回答、ありがとうございます。サンプルデータを確認させていただきました。
やりたいことは正にこれで、実際に自分のファイルでやってみたところ、うまくできました！
いつもこちらのやりたいことを理解した上での的確な回答に驚いてます。
どうもありがとうございました。助かりました。
すみません、もう一つ質問させてください。
月から3月まで1年のスライサーがあり、月ごとに科目の合計値があるテーブルにおいて、例えば4月から7月までスライサーで選択したときに、4ヶ月分の合計値ではなく、最後の7月分だけの合計値をカードに表示したいのですが、そのようなことは可能でしょうか。
複数の月を選択した場合、最後の月分の合計値だけを表示したい、ということなのですが・・・。
単月の選択のときはその月の合計値、何も選択されないときは最終月つまり3月の合計値を表示したいです。
先の質問もそうなのですが、Power BI Desktop上では条件分岐がなかなか難しいです。
よろしくお願いします。
立て続けの質問で恐縮です。ー 帳票イメージのデータをPowerBIに取り込んでいるので、例えば、「Customer (Avg.)」 というフィールド（文字）に、「18 (฿ 1,635.39)」とか、「18 (฿ 1,811.22)」、「1 (฿ 946.60)」 という風に入ってくるのですが、ここから、顧客数と客単価を数字として抜き出す事ができるのでしょうか？
動画作成しましたのでご確認いただけますか？よろしくお願いします！ua-cam.com/video/-s2vcvt9uyQ/v-deo.html
凄い！まるで魔法のようですね。てっきり文字を切り出す複雑な関数を書くのかと思っていたら、驚きました。ありがとうございました。
すいません、こんな事ができるなら、できないだろうと思って質問しなかったのですが、データの単位が１行出ない場合、実際にはあるカラムだけが膨れていて、１行、２行、３行となっているのです。
その膨れるカラムは、
2020年10月４日 JCB ฿ 718.00 (1)
MASTER ฿ 18,961.00 (3)
VISA ฿ 13,053.00 (2)
Cash ฿ 7,957.00 (1)
2020年10月５日 MASTER ฿ 18,961.00 (3)
VISA ฿ 13,053.00 (2)
Cash ฿ 7,957.00 (1)
とこんな感じなのです。こんなデータも取り扱う事が可能なのでしょうか？
PowerQueryのフィルを使えば可能です。動画リンクの1:38~ をご確認ください。ua-cam.com/video/4w9evvsLUWk/v-deo.html
ありがとうございます。このフィルは知ってはいるのですが、実は、このカラム以外の一列（日付と同列）のデータの方が多く、そーると、日付以外の他のカラムも全てフィルすることになるので、このカラムだけ複数行になっているので、このカラムだけフラット（１行）に戻せないのか？と思ったのですが、それは？無理ですよね？
こんばんは！質問なのですが、メジャー作成をするとき、親値のみしか式を作れないのでしょうか？(指揮を作成するとき参照したいデータが親値しかないようなので…)
返信遅れてすみません！Related関数を使えば他のテーブルの値も扱えると思います。後々解説動画を出そうと思います！
@@IT-ToolGakusyu ありがとうございます！さらに質問なのですが、テーブル上で、数値→パーセント表記にする方法を教えていただけないでしょうか。。
下記リンクの動画の38:52~で解説しています！ua-cam.com/video/Mfd_4XROoL0/v-deo.html
こんにちは。動画とは関係無い質問です。クエリで複数(フォルダーから)のExcelファイル(マトリックス)からデータ抽出と加工しました。A列には30項目の内容がExcelファイルの数分有ります。B列からZ列はその項目に伴う数値が入っています。行列変換でA列を一行目にしてヘッダーに設定すると、ヘッダーの項目が「不良数」「不良数1」というふうに別れてしまいます。行の時から別れていたので当然ですが。この列に置き換えた時に項目を纏めたいです。
どうすればよろしいでしょうか？
ご質問ありがとうございます！少し検討してみますね！
簡単に作ってみましたが、こんな感じでよろしいでしょうか。ご確認いただけますか？
drive.google.com/file/d/1UGrvTlVe1PM_K1xWGvv3UhcS7TfcHBJt/view?usp=sharing
ITツール学習 こんばんは。すみません映像無しで伝えるのが難しくて。少しイメージと違います。A列に項目がある時は「不良数」の名称で複数個有ります。行に変換しても同じです。ただヘッダーとして使用すると自動的に「不良数」「不良数1」というふうに数字が付きます。それで私が纏めたいのは「不良数」という項目で全てを纏めたいです。 最初からもう少し詳しく描きます。 クエリでで取り込んだ後の行列の構成です。A列二行目から「生産数」三行目「排出数」四行目「不良数」が有ります。B列1行に日付の1が有ります。C列1行は2で、31まで続きます。この構成のシートが6シートあります。ですのでA列五行目には生産数がまた入力されます。B列五行目には日付の1が入力されています。これを行列入れ替えするとA2から1
A3に2...31まで続いてA33は空欄でA34からまた1.....という6シート分続きます。行はB1に生産数C1に排出数D1に不良数E1は生産数とこちらも6シート分繰り返しですこれを一行目をヘッダーに使用すると生産数 排出数 不良数 生産数1 排出数1 不良数1となります。これを生産数 排出数 不良数の3つの列だけに纏めたいです。 こんな感じでご理解いただけたでしょうか？何かITツール学習様だけに写真などを送れる方法があれば写真で説明いたします。お手数掛けますがよろしくお願いします。
もう少し単純な説明を考えました。クエリエディタでcolumn1から3に生産数 排出数 不良数が有ります。4から6に生産数1 排出数1 不良数1が有ります。4から6の列ごと1から3の下に続けて表示したい。生産数1 排出数1 不良数1の表示行は削除したいです。
ご説明いただきありがとうございます。データの内容は理解できましたので綺麗にできないか確認してみますね！
すみません、下に回答いただいた続きで質問を記入させていただいたのですが、もしかしたらそのように続きで記入すると気がついてもらえないのかと考え、改めて質問させてください。
これもここで質問する内容ではないかもしれませんがご容赦ください。
お尋ねしたいのは、月から3月まで1年のスライサーがあり、月ごとに科目の合計値があるテーブルにおいて、例えば4月から7月までスライサーで選択したときに、4ヶ月分の合計値ではなく、最後の7月分だけの合計値をカードに表示したいのですが、そのようなことは可能でしょうか。
複数の月を選択した場合、最後の月分の合計値だけを表示したい、ということなのですが・・・。
単月の選択のときはその月の合計値、何も選択されないときは最終月つまり3月の合計値を表示したいです。
先の質問もそうなのですが、Power BI Desktop上では条件分岐がなかなか難しいです。
よろしくお願いします。
lastnonblankという関数を使えば実装できそうです。下記リンクにサンプルを作成しましたのでご確認いただけますか。drive.google.com/file/d/1Br0AdBVqvowkv-SQ6MVg8prSpvLSA3QF/view?usp=sharing
@@IT-ToolGakusyu 様、ご回答ありがとうございました！やはり難しいのかなぁと諦めていました。
とにかく早速確認させていただきました。正にこの通りです！いろいろな関数があるのですね。自分ではなかなか見つけられないです。
つたない説明なのにこちらの意図を読み取ってサンプルデータで回答いただき、大変助かりましたし、勉強になります。
実はスライサーなのですが、タイル形式で月を選択できるようにしています。月タイルを選択したときはサンプルデータの表示でバッチリなのですが、月タイルの選択をしない状態だと最終月の３月の合計値のみが表示されます。月タイルを無選択の状態のときは当月（今日の日付から判断）の合計値のみ表示したいという我儘な要求をされました。実務上ではこうした我儘な要求が出てきて閉口してしまいます。
大変恐縮なのですが、もうちょっとだけアドバイスをお願いできないでしょうか。もう動画からの質問としてはかけ離れてしまってご迷惑と思っておりますが他に頼れるところもなくて・・・。すみません。
DAX歴3年の通りすがりです。SUMX内の評価式(expression)で[売上]としていますが、DAXの基本はメジャーであれば
[メジャー名],
列名であれば
テーブル名[列名]
とすることがベストプラクティスですので、その規則に従った方が良いかと思います(この場合、SUMX(テーブル式, ’テーブル1’[売上])がベストプラクティス)。列を参照をしているのか、メジャーを参照しているのか、初心者には見分けが付きにくいのと、SUMXのexpressionには列や計算列以外に、メジャーも入るのが理由です。ただこの場合、Iterator関数(SUMXやMAXXなどの関数)内に完全な重複行がある場合、expressionがメジャーになってしまうとContext Transitionという概念により計算結果を間違って算出されてしまう可能性があるので、注意が必要です。[○○]はPower Queryであれば列参照ですが、DAXでは基本的にメジャー参照を意味しますので、面倒でも動画ではFILTER(テーブル1, テーブル1[製品名]...)として紹介したほうが列参照であることが瞬時に判別できますし、初心者の方が混乱しなくて済むかと思います。
ご教示いただきありがとうございます！確かに文字数が多くなっても、わかりやすい表現にした方がいいですね！今後の動画制作に参考にさせていただきます！context transition等の知らない言葉もありますので、勉強していきます！
@@IT-ToolGakusyu DAX is simple but not easyという金言がありますが、最初はExcelと同じ感覚で入門し、途中から気づけば迷子になり、ある日を境に覚醒する(急に分かるようになる)というパターンがDAXを最も適切に表現したものと言われています。日本語の文献が少ないのも普及しない原因の一つですが、理解できると非常にパワフルな言語だと思いますので、頑張って下さい。そういう私も何年やってもまだまだ上達の余地が残っていると思う日々です。
いかにわかりやすく説明できるか考えているのですが、なかなか良いアイデアが出てこないんですよね〜
とりあえず理解するより先に触ったほうが早かったりするかもですね！
以前質問させていたものですが、コメントがおそらく私のミスで消えていました。
また分からないことが出てきましたので、質問させていただきます。
Swich文による集計の切り替え方法についてお尋ねしたいです。実装したいことを簡単にですがPower Pointの資料に書きましたので、どうぞよろしくお願いいたします。
pbix
drive.google.com/file/d/1dHgbB3D24wyD_l5mOvZ3MDXzOmd0C_ne/view?usp=sharing
power point
drive.google.com/file/d/101hA8wPEhfyf9SLo1olj-GZVMDu8_QCK/view?usp=sharing
こちらが参考にさせていただいたサイトです。
note.com/tentomusea/n/n7b1d4398281a
自己解決できました。すみません…
もう１つだけ分からないことがあるのですが、テキストに準拠したカウント方法が分からないです。
BIファイル内で言えば、列[判定]が"高"になっている行数をカウントしたいです。
[判定]="高"のような条件でカウントすることは出来ないでしょうか？
drive.google.com/file/d/1OV1VnPzupV8eKu6j39jlfQVKggOXNlCt/view?usp=sharing
お返事が遅れてしまい申し訳ありません。検討してみますので少々お待ちください～
前回と同じようなDAX式で記述できました!Keepfilters関数についてはどこかで解説したいと思います！下記リンクをご確認ください！
drive.google.com/drive/folders/15JJvUooHD7OCG01AzoVBxVkt1FlW8wRi?usp=sharing
@ITツール学習 私も返信に気が付かず申し訳ございません、通知を見逃していたみたいです…。ありがとうございます、大変参考になりました。Keepfilters関数も含めDAX式の解説動画はとても参考になりますので、是非投稿していただきたいです！
もう一つ困っていることができたので質問させていただきます（この要件で返信に気が付くことができました…）。お力を貸していただきたいです。
下記のリンクに質問資料とpbixファイルを添付しましたので、確認のほどお願いいたします。
drive.google.com/file/d/1WYZynzIUFo85iMbBLL5HSl0bnYlg--1J/view?usp=sharing
drive.google.com/file/d/1XjqKVVvlUZ5jk3CV8Hf8A2dm7xoItQt5/view?usp=sharing
@@user-pl8th7tm8c さん 返信が遅くなってしまい申し訳ありません。強引なやり方ですが一応所望の列は作れたと思いますのでご確認をお願いします。
手順としては下記になります。
①「グループ化テーブル」というテーブルを新規で作成
。Summarize関数で集計する。
②カテゴリ名、商品名、注文年、注文期を結合したkeyという列を作成
③「データ1」にも②と同様のkeyを作成
④「グループ化テーブル」と「データ1」をkeyでリレーション
⑤「データ1」にrelatedを使った集計列を追加
よろしくお願いします！drive.google.com/file/d/1uLib126XAKrdA7RRdsoHbAJikDntFmPE/view?usp=sharing