使用 DAX 計算在 LuckyTemplates 中選擇特定日期

本教程將討論如何在LuckyTemplates 數據報告中選擇或篩選特定日期。本教程使用的素材來自。您將學習如何解決問題並了解所使用的方法。您可以在本博客底部觀看本教程的完整視頻。

目錄

• 了解 LuckyTemplates 中的數據報表
• 重新創建表格和可視化
  • 在 LuckyTemplates 中加載日期表和切片器
  • 為選擇切片器創建度量
• 創建計算表
• 在過濾器上下文中註入日期
• 使用 TREATAS 修復 LuckyTemplates 矩陣中的日期
  • 在 TREATAS 上使用 KEEPFILTERS 函數
• 修復 LuckyTemplates 矩陣中的缺失日期問題
  • 使用 DAX 解決 LuckyTemplates 中的缺失日期問題
• 在原始測量中使用 +0
• 結論

了解 LuckyTemplates 中的數據報表

這是將要使用的示例數據報告。它由一個表視覺和兩個切片器組成。

目標是在切片器上的日期列上進行日期選擇。這意味著一旦選擇了切片器中的日期，表格可視化將以 7 天為間隔顯示前幾天。在此示例中，日期切片器中選擇的數據是 2007 年 5 月 12 日。

該表應顯示前一個日期之間有 7 天的差異。

您還需要創建一個假設參數以根據選擇動態顯示日期數。如果您選擇3，則表格視覺對象應僅顯示 3 個日期。

重新創建表格和可視化

第一步是創建一個假設參數。在 LuckyTemplates 桌面中，轉到“建模”選項卡並選擇“新建參數”。

為參數添加一個名稱並將數據類型設置為整數。然後，設置最小值、最大值、增量和默認值，如下圖所示。通過選中該框將切片器添加到頁面。

按確定後，將生成一個切片器。通過單擊下拉按鈕並選擇列表，將切片器轉換為列表。

接下來要做的是創建一個重複的日期表，它將用作斷開連接的日期表。轉到強大的查詢編輯器並複制Dates。

然後，重命名重複的DisDate並單擊關閉並應用。

在 LuckyTemplates 中加載日期表和切片器

在 LuckyTemplates 模型中加載複製表後，使用斷開連接的日期表中的日期列創建一個切片器。打開 DisDate 表並將 Date 拖到畫布上。

然後，將可視化變成切片器。

之後，將切片器的格式更改為List。可視化將如下所示。

接下來，複製切片器並使用 Dates 表中的 Date 列更改數據字段。

從 Dates 切片器中選擇一個日期將限制 Sales 表在矩陣中的可見行數。如果您選擇 2005 年 3 月 14 日，矩陣將只顯示該日期。

如果您使用此設置，結果和度量將不起作用。原因是甚至在措施開始工作之前，矩陣的訪問就已經被過濾了。對於那些過濾後的值，您將無法生成具有間隔的日期。

為選擇切片器創建度量

當選擇放在切片器上時，您需要創建一個不直接過濾矩陣的日期設置。矩陣的過濾必須通過DAX 代碼而不是直接來自切片器。因此，如果您從 DisDate 切片器中選擇一個日期，則矩陣中不會發生任何變化。

發生這種情況是因為 DisDate 表與 Dates 表沒有關係。這就是斷開連接表必不可少的原因。

刪除帶有日期表的切片器並創建第一個度量。該度量值將檢索從 Dates To Show 切片器中選擇的值。您需要創建的第一個變量是ValuesToShow。接下來，將其等同於 Dates To Show 值並輸入RETURN以返回所選的值。

將該度量拖到矩陣中。如果您從 Dates To Show 切片器中選擇 6，則矩陣中的 Measure 列也將產生 6。

接下來，創建另一個變量，用於檢索在切片器中選擇的斷開連接日期。

完成後，您將看到在“測量”列中顯示的 DisDate 切片器中選擇的值。

創建計算表

從切片器中的選定日期開始，創建一個以 7 天為間隔的日期列表。然後，確保矩陣將根據顯示切片器選擇的日期生成天數。為此，您首先需要創建一個計算表。

計算表可幫助您直觀地了解日期是如何在 DAX 代碼中生成的。轉到數據視圖並單擊新建表。

輸入變量SelectedDate並將其等同於DATE函數。然後，在括號內輸入日期。接下來，創建另一個變量來指定要在矩陣中顯示的日期數。輸入DatesToShow並將其等同於日期數。

之後，創建另一個變量，該變量將生成以 7 天為間隔的日期列表。輸入DatesList並使用GENERATESERIES函數。 是一個通過傳入多個參數生成數字列表的函數。

對於第一個參數，輸入SelectedDate – (DatesToShow * 7)  作為起始值。接下來，輸入SelectedDate作為結束值。對於最後一個參數，輸入7作為增量值或日期之間的間隔。完成後，關閉函數並輸入RETURN和DateList。

然後您可以看到一個表格，其中包含從 5 月 5 日開始的七個值，並以七個為增量返回。

現在，您會注意到它創建的日期比您需要的多。在原始示例報表中，顯示的最後日期為 3 月 31 日，顯示日期和日期選項相同。

要解決此問題，您可以將 DatesToShow 變量值更改為 5 或從 6 中減去 1。在此示例中，從 6 中減去 1。

之後，您會看到這些值在 3 月 31 日結束。

在過濾器上下文中註入日期

接下來，您需要使用這些日期並將其註入到日期篩選器上下文中。您還需要在過濾器上下文中計算銷售額。因此，複製GENERATESERIES語法並返回到您的度量。

在度量中，添加一個新變量並輸入DatesToShow。然後，將該變量等同於GENERATESERIES語法。將語法中的變量從SelectedDate更改為DisSelectedDate，將DatesToShow更改為ValuesToShow。

最後，為結果創建一個變量。將其等同於 Total Sales 和 DatesToShow 上的CALCULATE函數，以將從GENERATESERIES語法接收的所有日期注入到過濾器上下文中。完成後，在RETURN函數中輸入結果。

現在，您會看到矩陣不會顯示 DAX 代碼請求的日期。如果您將 Total Sales 度量值帶入矩陣，您會看到代碼本身返回了銷售額。

該度量的問題在於，在GENERATESERIES語法中創建的日期具有與斷開連接的日期表的數據沿襲。這意味著 Date 表未連接到 Sales 表。這就是為什麼在過濾上下文中應用日期列表時，它不包括 Sales 表的原因。

您需要使用 函數來創建虛擬關係並應用過濾上下文。

使用 TREATAS 修復 LuckyTemplates 矩陣中的日期

您需要將從GENERATESERIES語法收到的值視為原始 Date 表的一部分。這基本上是用數據模型中日期表的日期列構建沿襲。

因此，在 Result 變量中，在 Total Sales 表後輸入TREATAS並提供參數。對於第一個參數，提供沒有關係的列或表。在這種情況下，使用DatesToShow列。

對於第二個參數，提供要將第一個參數中列的沿襲映射到的列。在此示例中，使用了 Dates 表中的 Date 列。

但是，即使在代碼中進行了更正，矩陣中的 Measure 列在使用切片器時仍然不會顯示正確的值。您還可以注意到列中的值是重複的。

問題在於代碼的編寫方式。TREATAS將所有值應用到過濾器上下文中，使用從GENERATESERIES函數中檢索到的所有值覆蓋選定日期的現有過濾器上下文。

在 TREATAS 上使用 KEEPFILTERS 函數

要確保在不覆蓋現有過濾器上下文的情況下將過濾器注入過濾器，您需要使用KEEPFILTERS函數。 改變了CALCULATE的過濾語義。

在TREATAS功能之前輸入KEEPFILTERS並確認測量。

您現在會看到 Measure 列中的值沒有重複。

如果展開 5 月和 4 月，您會看到 Measure 列中的值等於 Total Sales 列中的值。

刪除矩陣中的 Total Sales 度量並在 Dates To Show 切片器中選擇一個數字。然後，您會看到矩陣中顯示的日期數是相等的。

修復 LuckyTemplates 矩陣中的缺失日期問題

此報告的另一個問題是，當您在DatesToShow切片器中選擇 8 時，矩陣不會更改。

如果您在切片器中選擇 9，則矩陣僅顯示 8 個日期。您還會注意到日期列表中缺少 3 月 17 日。

原因是在 Date 表中的某些天，Sales 表中沒有匹配的交易。因此，該度量為這些日期返回空白。由於它返回空白，因此在矩陣後面運行的代碼會從結果中刪除空白行。

LuckyTemplates 然後使用返回的結果來填充矩陣。因此，由於有些日子返回空白，您無法在矩陣中看到那些日子的銷售額和條目。

如果您想顯示數據中沒有銷售額的日子的某些內容，請返回度量並刪除CALCULATE語法。

使用 DAX 解決 LuckyTemplates 中的缺失日期問題

通過在Dates表的DatesToShow和Date列上寫入TREATAS來創建數據沿襲。然後，檢查TREATAS函數返回的所有日期是否都包含在過濾器上下文中。

從那裡，將執行拆分為多個變量。將變量名稱從Result更改為CreateDataLineage以表明它存儲了TREATAS的結果。

然後，創建另一個變量，該變量將保存來自矩陣的過濾器上下文中可見的所有日期。輸入VisibleDate作為變量名稱，並在日期列上使用 。

接下來要做的是在TREATAS函數的結果上創建一個包含銷售額的列。將DatesWithSales寫為變量名，並在CreateDataLineage變量上使用 。

在函數內部，創建一個新列並將其命名為Sales Amount，並在行上下文中使用 Total Sales 度量來啟動上下文轉換。

之後，創建一個變量來檢查DatesWithSales變量中存儲的所有日期是否都包含在過濾器上下文中。輸入IsDateInFilterContext作為變量名並使用 函數。

從那裡，將DatesWithSales寫為函數的第一個參數。接下來，輸入Dates[Date] IN VisibleDate作為第二個參數，以檢查DatesWithSales表中包含的日期是否包含在VisibleDate變量中。

您需要創建的最後一個變量是Result。在IsDateInFilterContext變量上使用 函數。寫入[@Sales Amount] + 0以包含之前未包含的天數。

之後，您將在矩陣中看到缺失的日期，即 3 月 17 日。

在原始測量中使用 +0

前面的步驟向您展示瞭如何構建一個度量以包括事實表中沒有交易的所有日期。現在，如果您在創建的原始度量中使用+ 0 ，則 Measure 列中的值將全部為 0。

原因是Result變量包含空白或銷售額。因此，如果您添加 0，則用 0 替換空白。請記住，匯總列會從數據集中刪除空白行。將它們返回到 LuckyTemplates，以便它們只能顯示有限的行而不是空白。

但是由於添加了 0，因此匯總列被迫保留行。然後它返回一個表，其中包含 Dates 表中存在的所有日期，並在適用或空白時顯示零。

This the reason why you need to rewrite the measure in a way where you only need to add 0 to the dates that are visible in the filter context and are included in the DatesToShow variable.

Power Automate Connectors: Number, Text, And Date Time
Extended Date Table Power Query M Function
Power Query LuckyTemplates | Create New Records Based On Date Fields

Conclusion

To create a data report that lets you select a specific date in a LuckyTemplates matrix, you need to use calculated tables and DAX functions. DAX functions and measures need to be written in a way that can easily obtain results.