初投稿です。
https://qiita.com/quincedaddy/items/6a90c6c0da829dd59584
こちらの記事に大変お世話になったので自分でも書いてみようと思いました。
twitterで教えてくださった@hrkasnoさんありがとうございます！！
存在しない欠損データを埋めてグラフを実際に近い形に近づけよう
データをしっかり整えて、より実際的できれいなグラフを描こう！という記事です。
PowerBIでグラフがデータ欠損部分を無視して直線を描いてしまう問題
BIツールって手軽に動的なビジュアライズできるのが良いとこだよね
PowerBIって何がいいかって、グラフを自動で更新してくれたりとか、大量のデータをつかっても手軽にビジュアルをつくってくれるところですよね。しかもグラフにマウスを重ねることでいろいろ情報表示してくれたり、詳しい情報を出してくれたり、パワポとかのただの画像じゃんっていうようなグラフよりもリッチ！！触っていてめちゃくちゃ楽しい！！
が、そのビジュアル化がうまくいかないと、PowerBI使う意味がありません。
でもPowerBIってデータ欠損部分のグラフ、勝手に直線引いちゃうじゃん
今回直面したのが、欠損しているデータ（特に今回は、０が入っているわけでも、空白なわけでもない、存在しないデータを扱います）部分があるとき、PowerBIの挙動として、二点があったら直線が引かれてしまうこと、一点のみならただの点になってしまうこと。
例として、各時間にデータがどのくらいあったのかカウントしたものをあげます。
みたいな感じです。本当だったらAの13時以降とか、Cの10時、15時以降のデータもゼロとして表示してほしいのに。。
あと、Cの12時は本当はカウント０のはずなのにグラフとしてはあたかも4.5くらいあるように見える！！
本当はこういうグラフを作りたいんです
でも、たぶんPowerBIでデータ欠損している部分を勝手にゼロで埋めてくれるという機能は今のところない、、はず。
ということで、二つ目のグラフみたいに、ダミーデータというか、ゼロという数字が入ったデータを足してやらないといけないのです。
ダミーデータを入れて対処する方法
僕がこの問題に直面したときは、行数百万件以上ある、いわゆるビッグデータでした。
まさか手動ではできないし、Excelで処理にするにも行の上限をオーバーしてしまうかもしれない。
かといってPythonとかで書くのもめんどくさい。
というか、特に時系列データって日々更新していくからこそ価値があるもので、できれば追加で吐き出されてきた素のCSVをPowerBIに投げるだけで更新終わりにしたいですよね。あんまり間にプログラムもはさみたくない。
と、いうことで、PowerBIでダミーデータつくるのも完結させようというのが今回の趣旨です。
2列（あるいはそれ以上）のかけ合わせのテーブル（マトリクス）をつくる
その部分がNULLとかでデータのレコード、行自体は存在しているなら楽です。
列の追加で適当なDAX関数使ってやればよい。今回は割愛します。
問題は、そもそも行が存在しないことです。
では、どうすればよいか。
該当する列をクロス結合していけばよいのです。
クロス結合って何ぞや、というと、列同士の全部の組み合わせを作ることです。
たとえば[A,B,C]ていうカテゴリと、[10:00,11:00,12:00,13:00]という時間があったら、
組み合わせは、[A-10:00,A-11:00,A-12:00…C-12:00,C-13:00]という3×4の12通りになりますよね。
それを全部つくって、データがある場合は呼び出してきて、データがなければ0を入れてやればよいのです。
それにはDAX関数の"CROSSJOIN"を使います。
DAX関数CROSSJOINの使い方
CROSSJOIN関数は、CROSSJOIN(テーブル1,テーブル2[,テーブル3...]）という形で使います。
「モデリング」の「計算」から「新しいテーブル」をつくってやり、この関数を入れれば全部の組み合わせができてしまいます。ですが、テーブル1やテーブル2には、列は入りません。あくまでもテーブルです。
なので、列から重複を除いた一列のテーブルをつくってくれる関数DISTINCTを使います。
DISTINCTはDISTINCT(列)とするだけ。簡単ですね。
ということで、新しいテーブルを作ってこんな感じの式を入れてやります。
これで、２つの列の掛け合わせテーブルができたので、後はそこにデータを入れつつ、データがないところに０を埋めていくだけです。
CALCULATE関数とCOUNTA関数、FILTER関数でExcelでいうところのCOUNTIFを実装
ということで、「モデリング」の「計算」から「列の追加」をして、
こんな感じの式を入れてやるだけ。
COUNTA関数で全データ数を数えて、各行でじかんが一致するものと、カテゴリが一致するものだけにフィルタリングしています。
詳しくは「PowerBI COUNTIF」とかで調べると出てくるかも。
最後の＋０がミソですね。最初にあげたURLから教えていただきました。
これがないとNULL値だと判定されてデータが入らず、グラフにも表示されないんです。
あとは折れ線グラフだったら軸にじかん、凡例にカテゴリ、値に新しく追加した列を指定するだけで実際の数値に近いきれいなグラフが描けます。
以上になります。初投稿ですがうまく書けたかなぁ。
次は、VLOOKUP的なことをしようとして詰まったので、そういったところと、あとヒント表示の編集に感動したのでそういうの書きたいですね。