Power BI Desktop 中 GROUP BY 的最小值 [英] Min value with GROUP BY in Power BI Desktop

问题描述

id  datetime             new_column           datetime_rankx
1   12.01.2015 18:10:10  12.01.2015 18:10:10  1
2   03.12.2014 14:44:57  03.12.2014 14:44:57  1
2   21.11.2015 11:11:11  03.12.2014 14:44:57  2
3   01.01.2011 12:12:12  01.01.2011 12:12:12  1
3   02.02.2012 13:13:13  01.01.2011 12:12:12  2
3   03.03.2013 14:14:14  01.01.2011 12:12:12  3

我想创建一个新列，该列将具有按 id 分组的每一行的最小日期时间值.

I want to make new column, which will have minimum datetime value for each row in group by id.

我如何在 Power BI 桌面中使用 DAX 查询来做到这一点?

How could I do it in Power BI desktop using DAX query?

推荐答案

使用这个表达式:

NewColumn =
  CALCULATE(
    MIN(
      Table[datetime]),
      FILTER(Table,Table[id]=EARLIER(Table[id])
   )
  )

在 Power BI 中使用包含您的数据的表，它将生成:

In Power BI using a table with your data it will produce this:

更新:说明及EARLIER函数用法.

基本上，EARLIER 函数可以让您访问不同行上下文的值.

Basically, EARLIER function will give you access to values of different row context.

当您使用 CALCULATE 函数时，它会创建整个表的行上下文，理论上它会遍历每个表行.当您使用 FILTER 函数时也会发生同样的情况，它将遍历整个表并根据过滤条件评估每一行.

When you use CALCULATE function it creates a row context of the whole table, theoretically it iterates over every table row. The same happens when you use FILTER function it will iterate on the whole table and evaluate every row against the filter condition.

到目前为止，我们有两个行上下文，由 CALCULATE 创建的行上下文和由 FILTER 创建的行上下文.注意 FILTER 使用 EARLIER 来访问 CALCULATE 的行上下文.话虽如此，在我们的例子中，对于外部(CALCULATE 的行上下文)中的每一行，FILTER 返回一组对应于外部上下文中当前 id 的行.

So far we have two row contexts, the row context created by CALCULATE and the row context created by FILTER. Note FILTER use the EARLIER to get access to the CALCULATE's row context. Having said that, in our case for every row in the outer (CALCULATE's row context) the FILTER returns a set of rows that correspond to the current id in the outer context.

如果你有编程背景，它会给你一些意义.它类似于嵌套循环.

If you have a programming background it could give you some sense. It is similar to a nested loop.

希望这段 Python 代码指出这背后的主要思想:

Hope this Python code points the main idea behind this:

outer_context = ['row1','row2','row3','row4']
inner_context = ['row1','row2','row3','row4'] 

for outer_row in outer_context:
    for inner_row in inner_context:
        if inner_row == outer_row: #this line is what the FILTER and EARLIER do

            #Calculate the min datetime using the filtered rows
            ...
            ...

<小时>

更新 2:添加排名列.

要获得所需的排名，您可以使用以下表达式:

To get the desired rank you can use this expression:

RankColumn = 
  RANKX(
    CALCULATETABLE(Table,ALLEXCEPT(Table,Table[id]))
    ,Table[datetime]
    ,Hoja1[datetime]
    ,1
 )  

这是带有排名列的表格:

This is the table with the rank column:

如果这有帮助，请告诉我.

Let me know if this helps.

这篇关于Power BI Desktop 中 GROUP BY 的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！