在Power BI中建立簡單且圖表友好的日期表

本文翻譯自國際Power BI大師Marco Russo的文章——《Creating a simpler and chart-friendly Date table in Power BI》，透過利用自定義格式字串來充分控制圖表的視覺化和排序順序，Power BI中的日期表可以具有較少的列數。

建立一個經典的日期表

日期表通常含有月份和星期之類的列，這些列應按照日期順序排列-而不能對日期和月份使用首字母排序。為此，我們建立兩列：具有可見名稱的一列，該列由包含數字的另一列排序。例如，在以下DAX計算表中，月份按月份編號排序：

Classic Date =

VAR FirstYear = —— Customizes the first year to use

YEAR （ MIN （ Sales［Order Date］ ） ）

RETURN

ADDCOLUMNS （

FILTER （

CALENDARAUTO （），

YEAR （ ［Date］ ） >= FirstYear

），

“Year”， YEAR （ ［Date］ ），

“Year Month”， FORMAT （ ［Date］， “mmm yyyy” ），

“Year Month Number”， YEAR （ ［Date］ ） * 12 + MONTH （ ［Date］ ），

“Month”， FORMAT （ ［Date］， “mmm” ），

“Month Number”， MONTH （ ［Date］ ），

“Day of Week”， FORMAT （ ［Date］， “ddd” ），

“Day of Week Number”， WEEKDAY （ ［Date］， 1 ）

）

在先前的程式碼中，月份（Jan， Feb， …）有12個名稱，年份-月份有12個名稱（Jan 2008， Feb 2008， …， Dec 2008， Jan 2009， Feb 2009， …）。

年份-月份對於建立類似於下圖的圖表很有用。

如果折線圖有限的區域中包含許多資料點，則底部會顯示為捲軸。實際上，因為X軸包含文字而不是數字，所以它是分類型別的。在“使用DAX改進Power BI中的時序折線圖”一文中，我們展示瞭如何在日期表中建立其他列以設定X軸上的連續型別。該附加列包含每個時間段的第一天或最後一天，以折線圖表示。這種方法的唯一缺點是，日期表中需要增加屬性的數量，但結果卻是我們所期望的：所有資料點在折線圖中都是可見的，沒有捲軸，而X軸根據可用的空間調整描述的密度。

建立更簡單的日期表

隨著Power BI中自定義格式字串的最新引入，我們可以使用另一種方法，該方法不再需要其他列，並且還不需要使用“按列排序”。基本思想是，我們總是儲存日期，例如年，月或周。自定義格式字串顯示我們要在使用者介面中看到的文字；但是，由於基礎資料是數字，因此我們會自動獲得正確的排序順序，並能夠在折線圖的X軸上使用“連續型別”。

這是我們對日期表的新定義：

Date =

VAR FirstYear = —— Customizes the first year to use

YEAR （ MIN （ Sales［Order Date］ ） ）

RETURN

ADDCOLUMNS （

FILTER （

CALENDARAUTO （），

YEAR （ ［Date］ ） >= FirstYear

），

“Year”， DATE （ YEAR （ ［Date］ ）， 12， 31 ），

“Year Month”， EOMONTH （ ［Date］， 0 ），

“Month”， FORMAT （ ［Date］， “mmm” ），

“Month Number”， MONTH （ ［Date］ ），

“Day of Week”， FORMAT （ ［Date］， “ddd” ），

“Day of Week Number”， WEEKDAY （ ［Date］， 1 ）

）

如果沒有正確的列格式，那麼年和年-月列的結果將難以讀取：分別是一年中的最後一天和月份中的最後一天。

可以在圖表檢視中修改列的自定義格式字串屬性。例如，這是應用於年-月列的自定義格式。

我們將以下格式應用於日期表的日期時間資料型別列：

日期：m / dd / yyyy（1/14/2008），用作標記為日期表的列

年：yyyy（2008）

年-月：mmm yyyy（2008年1月）

我們仍然使用經典方法，將文字列和數字列用於“月”和“周”屬性的排序順序。這是因為我們不太可能對這兩個屬性使用連續折線圖。帶有格式化列的表如下。

即使格式語法與FORMAT函式支援的格式語法相似，您也必須注意一些區別和限制。例如，沒有語法可顯示日期的四分之一數字。您可以在Microsoft文件中找到有關支援的自定義格式語法的所有詳細資訊。

（文件連結：

https：//

docs。microsoft。com/en-u

s/power-bi/create-reports/desktop-custom-format-strings#supported-custom-format-syntax

）

注意：如果您想在格式化字串中應用字首，則在每個常量字元之前使用反斜槓字元（\），而這些字元不必被解釋為自定義格式字串。例如，對於具有財年的列，應使用表示式\ F \ Y yyyy，以便獲得2008財政年度的結果。

Date表具有層次結構，即使這不會對格式產生副作用：

日曆：年（年），月（年-月），日期（日期）

透過使用新的日期表，我們獲得以下矩陣，其中“年月”和“月”列顯示所需的文字而不是基礎日期。

這種方法的另一個優點是，如果您為DAX計算操縱過濾器上下文，則不必擔心按列排序所使用的附加列。

當我們使用折線圖導航“年-月”列時，我們得到了想要的結果。

侷限性和其他見解

在代表年和月（包括年）的列中使用日期資料型別是一種控制顯示格式並繼續使用折線圖X軸上的分類型別的便捷方法。不幸的是，我們不能對刻（15分鐘）使用這種技術，因為自定義格式語法不支援刻（15分鐘）。因此，對於日曆層次結構的刻（15分鐘）級別，我們應該使用兩列的經典方法：一列包含字串（如Q1-2008），一列包含排序順序。僅當使用自動提供正確排序順序的命名約定（例如2008-Q1而不是Q1-2008）時，才可以避免使用其他排序列。

當在帶有日期時間資料型別的“年”和“年-月”之類的列上使用“處於中間”條件的切片器時，即使基礎粒度是按年或月，該切片器也會始終顯示所有可能的日期。如果相應的日期在該範圍內，則選擇將僅包括整個月或全年，但是視覺化可能會誤導報表使用者。

在前面的示例中，我們繼續對“月”和“周”屬性使用兩列方法。不管日期表中的年數如何，這些列分別僅包含12和7個值。因此，不可能定義相應的日期，因為一月份彙總了日期表中所有年份一月份的日期。

但是，可以為所有一月日期分配一個任意日期，為所有星期一分配另一個日期。透過正確設定自定義格式字串，使用者將看不到差異，並且對於這些屬性，模型僅需要一列而不是兩列。儘管這可以簡化過濾上下文的操作，並且可以被認為是更“優雅”的解決方案，但實際上，它並沒有為終端使用者提供真正的優勢。當將“月份”或“周”屬性之一應用於折線圖時，預設情況下，他們將看到任意日期。在這種情況下，應將“ X軸型別”從“連續”更改為“分類”，以恢復正確的視覺化效果。因為本文的目的是簡化與報表使用者的互動，所以我們在建議的解決方案中未使用此方法。

結論

透過使用自定義格式字串，較簡單的日期表的列數可以減少。如果您操作過濾器上下文，則刪除按列排序要求也可以簡化DAX程式碼。只有一種警告與Power BI和DAX中FORMAT函式使用的格式語法之間的細微差別有關。使用Microsoft文件中支援的自定義格式語法作為Power BI的參考。

延伸閱讀：

使用DAX控制切片器顯示

DAX驅動視覺化——使用DAX自定義KPI圖

Power BI視覺化 | 自定義視覺化頁面主題

一文了解Power BI中DAX公式編輯器的常用快捷鍵

利用DAX實現動態顯示圖表標題