本文翻譯自國際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]
,
)
,
"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-us/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實作動态顯示圖表标題
如果您想深入學習微軟Power BI,歡迎登入網易雲課堂試聽學習我們的“從Excel到Power BI資料分析可視化”系列課程。點選左下角“
長按下方二維碼關注“Power Pivot工坊”擷取更多微軟Power BI、PowerPivot相關文章、資訊,歡迎小夥伴兒們轉發分享~