(Power BI) マトリックスで空白にゼロを表示させたい

7712 ワード
PowerBI DAX PowerBI テキストリンク

（この記事は2022/03/23に作成しました）

　値を表示する場合に、ゼロや空白はそれぞれ意味があります。計算する値が存在しない場合は空白になり、計算した結果が数値やゼロになります。それを踏まえたうえで、ゼロと空白についての扱い方を見ていきます。

空白をゼロにしたい

発端

　以下のようなデータがあります。

　値は、以下のDAX式です。

SalesAmount = SUM(Sales[Amount])

　この空白部分は、売上データデータが存在しないためです。
　ユーザー側から「いいから、データが無いときはゼロが表示されるようにしてくれ」とデータエンジニアに依頼が出されることがあります。ユーザーは ゼロが表示されるが当然 だと思っていますが、データエンジニアは 値がない ことと 値がゼロ であることの意味は違う！と考えていますが説得できず、ユーザーとデータエンジニアの間で短い睨みあいになる場面が往々にして発生します。

+0 で簡単にゼロ表示

　いやいやながら、以下のようにDAXを書き直します。

SalesAmount_1 = SUM(Sales[Amount]) +0


　空白の部分にゼロが入りますが、右に列が追加されてしまいました。
　対象外の年までゼロが表示されてしまうのは、 日付テーブルが開始年から現在の年までの日付を持っているため 、2021年5がから現在までの年までがゼロで表示されてしまうのです。しかし、明らかに必要のない年までゼロが表示されてしまうままで提出したのでは、嫌みととられかねません。解決策としては、日付テーブルを2021年1月から2021年4月までに設定してやればいいのですが、その他の製品のデータもレポートの中にあると影響が出る可能性があるので、ここでは日付テーブルの修正を行わないこととします。

対象製品の販売実績の開始と終了の日付を使う

　相手の要件に過剰不要なゼロ表示を消すための方法として、製品の取り扱い開始、終了の日付を調べ、その間で売り上げのない期間をゼロとすることが考えられます。

SalesAmount_5 = 
// 全商品の売上データ最初の日付
VAR FirstSaleEver =
    CALCULATE ( MIN ( Sales[SalesDate] ), REMOVEFILTERS () )
// 全商品の売上データ最後の日付の月末
VAR LastSaleEver =
    CALCULATE ( EOMONTH( MAX( Sales[SalesDate] ), 0 ) , REMOVEFILTERS () )
// 計算対象日付
VAR CurrentDate =
    MAX ( 'DateTable'[Date] )
// ゼロ表示するか判定
VAR ForceZero = FirstSaleEver <= CurrentDate
    && CurrentDate <= LastSaleEver
// ゼロ表示する場合は０を加える
VAR Amt =
    [SalesAmount] + IF ( ForceZero, 0 )
RETURN
    Amt

　Muffinは、まったく売上がないのに表に表示されています。これを外したい場合は、全期間の製品の売り上げが０以上であるか判定を入れるようにします。

SalesAmount_5 =
// 全商品の売上データ最初の日付
VAR FirstSaleEver =
    CALCULATE ( MIN ( Sales[SalesDate] ), REMOVEFILTERS () ) 
// 全商品の売上データ最後の日付の月末
VAR LastSaleEver =
    CALCULATE ( EOMONTH ( MAX ( Sales[SalesDate] ), 0 ), REMOVEFILTERS () ) 
// 計算対象日付
VAR CurrentDate =
    MAX ( 'DateTable'[Date] ) 
// ゼロ表示するか判定
VAR ForceZero =
    FirstSaleEver <= CurrentDate
        && CurrentDate <= LastSaleEver
        && CALCULATE ( Sales[SalesAmount], ALLEXCEPT ( 'Sales', 'Products' ) ) > 0 
// ゼロ表示する場合は０を加える
VAR Amt =
    [SalesAmount] + IF ( ForceZero, 0 )
RETURN
    Amt

製品ごとの販売開始、終了をみる

　前のものは、製品が販売開始されていなくてもゼロ表示されてしまいます。販売開始されているかどうかは、製品ごとの取り扱い開始、終了の日付を持つテーブルを使用することが望ましいのですが、ここでは売上テーブル上で値が最初に入っている年を取り扱い開始、最後に値が入っている年を取り扱い終了として計算してみます。

SalesAmount_2 =
// 製品ごとの売り上げ初日
VAR FirstDateWithSales =
    CALCULATE ( MIN ( Sales[SalesDate] ), ALLEXCEPT ( sales, 'Products' ) ) 
// 売り上げ全体の最終日
VAR LastDateEver =
    CALCULATE (
        EOMONTH ( MAX ( Sales[SalesDate] ), 0 ),
        ALLEXCEPT ( sales, 'Products' )
    )
VAR CurrentDate =
    MAX ( DateTable[Date] ) 
// ゼロ表示の判定
VAR ForceZero = FirstDateWithSales <= CurrentDate
    && CurrentDate <= LastDateEver
RETURN
    [SalesAmount] + IF ( ForceZero, 0 )

　最終日を製品ごとではなく、全製品の最終日に揃える場合は以下のようになります。

SalesAmount_2 = 
// 製品ごとの売り上げ初日
VAR FirstDateWithSales =
    CALCULATE(
        MIN(Sales[SalesDate]),
        ALLEXCEPT(sales,'Products')
    )
// 売り上げ全体の最終日
VAR LastDateEver =
    CALCULATE(
        EOMONTH( MAX(Sales[SalesDate]), 0 ),
        REMOVEFILTERS()
    )
VAR CurrentDate = MAX(DateTable[Date])
// ゼロ表示の判定
VAR ForceZero = FirstDateWithSales <= CurrentDate && CurrentDate <= LastDateEver
RETURN
    [SalesAmount] + IF(ForceZero,0)

　ここまでやるのは、やりすぎかもしれません。

ゼロ表示判定を別のレイヤにしたい

　先の例では、ファクトテーブル上にDAXでゼロ表示判定を書き込んでしまったため、データが汚れた状態になっています。ファクトテーブルに余計なものを追加せず、別テーブルでゼロ表示する・しないの判定を行うようにしてみます。

ZeroGain
ZeroGrain = 
// 売り上げ全体の最終日
VAR MaxSale = MAX(Sales[SalesDate])
// 製品ごとの売り上げ初日
VAR ProdsAndDate =
    GENERATE(
        FILTER('Products', [SalesAmount] > 0),  // 全期間で売り上げがあるかどうか判定
        VAR FirstSaleOfProduct = CALCULATE(MIN('Sales'[SalesDate]))
        VAR Dates = DATESBETWEEN('DateTable'[Date], FirstSaleOfProduct, MaxSale)
        RETURN 
            Dates
    )
// 
VAR Result = 
    SELECTCOLUMNS(
        ProdsAndDate,
        "ProductID", 'Products'[ProductID],
        "Date", 'DateTable'[Date]
    )
RETURN
    Result

　このテーブルを使って、ゼロ表示するかどうかDAX式で判定します。

SalesZeroWith
SalesAmount_4 = 
VAR ForceZero = COUNTROWS(ZeroGrain) > 0
RETURN
    [SalesAmount] + IF(ForceZero,0)

CalculationGroupを使う

　外部ツール Tabular Editor を使って CalculationGroup を作成してゼロ表示させることもできます。

Show Blanks
SELECTEDMEASURE()
Show zeros
VAR ForceZero = COUNTROWS(ZeroGrain) > 0
VAR Result = SELECTEDMEASURE() + IF(ForceZero,0)
RETURN
    Result

　スライサーの選択で、ゼロ表示する・しないの選択ができるようになりました。