Aggregate functions are one of the main building blocks in Power BI. Being used explicitly in measures, or implicitly defined by Power BI, there is no single Power BI report which doesn’t use some sort of aggregate functions.

聚合功能是Power BI中的主要構建塊之一。 由于在度量中顯式使用或由Power BI隱式定義，因此沒有單個Power BI報告不使用某種聚合函數。

聚合函數是什么？ (What are the aggregate functions?)

Aggregating means combining values in your data performing some mathematical operation. That can be SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT, COUNT DISTINCT, MEAN, STANDARD DEVIATION, etc.

匯總是指將數據中的值組合起來以執行一些數學運算。 可以是SUM，AVERAGE，MAXIMUM，MINIMUM，COUNT，COUNT DISTINCT，MEAN，標準偏差等。

However, in-depth observation of aggregate functions is not in the scope of this article. Here, I wanted to demonstrate how you can use aggregate functions in an unconventional way, since I believe it can be useful in some specific scenarios.

但是，對聚合函數的深入觀察不在本文的范圍之內。 在這里，我想演示如何以一種非常規的方式使用聚合函數，因為我認為它在某些特定情況下很有用。

聚合函數的默認行為 (Default behavior of aggregate functions)

By default, aggregations are being calculated on columns. Let’s take a look at following basic example:

默認情況下，聚合是在列上計算的。 讓我們看下面的基本示例：

This is a typical example of SUM aggregate function. Numbers are being aggregated on Year and Month level, and finally, in the end, we can see the total of individual values in the table.

這是SUM聚合函數的典型示例。 數字在“年”和“月”級別上進行匯總，最后，最后，我們可以在表中看到各個值的總數。

We could also perform AVERAGE to find average values, MIN or MAX to find the minimum and maximum values, etc. Pretty straightforward and probably already known for most of the people who ever worked with Power BI or Excel.

我們還可以執行AVERAGE來查找平均值，使用MIN或MAX來查找最小值和最大值，等等。這非常簡單，對于使用Power BI或Excel的大多數人來說可能已經眾所周知。

行上的聚合-毫不動搖！ (Aggregation on rows — without unpivoting!)

But, what if we wanted to perform aggregations on rows instead of columns? Is it possible to do that? And if yes, how?

但是，如果我們想對行而不是對列執行聚合呢？ 有可能這樣做嗎？ 如果是的話，怎么辦？

Let’s head over to a Power BI and check immediately. I have an Excel file as a data source and a dummy table which contains data about the customer and first date within a single year when he made a purchase:

讓我們轉到Power BI并立即檢查。 我有一個Excel文件作為數據源，還有一個虛擬表，其中包含有關客戶和他在購買后一年內的首次約會的數據：

As you can see, some customers made a purchase in every single year, some have gaps, some came in later years, etc.

如您所見，有些客戶每年都進行購買，有些客戶有缺口，有些則是在以后幾年等等。

Now, I want to retrieve the earliest date when a customer made a purchase, so I can later perform analysis based on that date (for example, to analyze how many customers made first purchase in February 2017).

現在，我想檢索客戶進行購買的最早日期，以便稍后可以基于該日期進行分析(例如，分析在2017年2月進行首次購買的客戶數量)。

I know, most of you would probably go with Power Query transformation and Unpivoting years’ columns, something like this:

我知道，你們中的大多數人可能會使用Power Query轉換和Unpivoting年的列，如下所示：

And you get a nice new look of the table, with all dates grouped by customer:

您會看到表格的新外觀，所有日期均按客戶分組：

However, an additional workload is necessary to build a separate column which will hold data about the earliest date (or MIN date) for every single customer, so we can later use this column for filtering purposes, or even for building a relationship to a date dimension.

但是，需要額外的工作量來構建一個單獨的列，該列將保存有關每個客戶的最早日期(或MIN日期)的數據，因此我們以后可以將該列用于過濾目的，甚至建立與日期的關系。 尺寸 。

What if I tell you that you can do this with a single line of code and without any additional transformations?

如果我告訴您可以用一行代碼執行而無需任何其他轉換，該怎么辦？

First, I will close the Power Query editor and go straight to the Power BI Data view:

首先，我將關閉Power Query編輯器并直接進入Power BI Data視圖：

You see that this table looks exactly the same as in Excel. Now, I choose to create a new column and, when prompted, enter following DAX code:

您會看到此表看起來與Excel中的表完全相同。 現在，我選擇創建一個新列，并在出現提示時輸入以下DAX代碼：

First Purchase Date = MINX({Sheet2[2016],Sheet2[2017],Sheet2[2018],Sheet2[2019]},[Value])

Let’s stop here for the moment and explain what we are doing. So, we want to extract the minimum date from every single row. We could do that by using multiple nested IF statements and using MIN aggregate function. Since MIN function accepts only two arguments, we would have multiple levels of nested IF statements, which is quite ugly and pretty much hardly readable.

讓我們暫時在這里停止并解釋我們在做什么。 因此，我們要從每一行中提取最小日期。 我們可以通過使用多個嵌套的IF語句并使用MIN聚合函數來做到這一點。 由于MIN函數僅接受兩個參數，因此我們將具有多層嵌套的IF語句，這很難看而且很難讀。

The magic here is in the curly brackets! By using them, we are telling DAX that we want it to create a table from the list within the curly brackets, and using MINX iterator aggregate function, we are simply iterating through this table and pulling minimum value from it.

魔術在花括號中！ 通過使用它們，我們告訴DAX我們希望它在大括號內的列表中創建一個表，并使用MINX迭代器聚合函數，我們僅在該表中進行迭代并從中獲取最小值。

How cool and elegant is that! It worked like a charm and here is the resulting column:

那是多么的酷和優雅！ 它像一種魅力一樣工作，這是結果列：

You can easily spot that DAX returned expected values, so now we can use this column as an axis in our charts, create regular date hierarchies on it, or we can even create a relationship between First Purchase Date and date dimension in our data model if we like to.

您可以輕松地發現DAX返回了期望值，因此現在我們可以將該列用作圖表中的軸，在其上創建常規的日期層次結構，或者在以下情況下甚至可以在數據模型中的“首次購買日期”和日期維度之間建立關系：我們喜歡。

結論 (Conclusion)

Power BI and DAX are full of hidden gems. In complete honesty, I have to admit that you might not face a scenario like this every single day, but in some specific situations, it’s good to know that you can perform aggregate functions on row level in a very simple, yet powerful manner — using a literally single line of code!

Power BI和DAX充滿了隱藏的寶石。 老實說，我必須承認您可能不會每天都遇到這樣的情況，但是在某些特定情況下，很高興知道您可以以非常簡單但功能強大的方式在行級別執行聚合函數-使用真正的單行代碼！