通过在DAX中的第二列上过滤来对一列的排名值 [英] Rank Values of one column by filtering on 2nd column in DAX

问题描述

我正在尝试使用RANKX公式对一列的值进行排名，但对第二列的值进行了过滤。在此示例中，col2是一个简单的计数器，以递增值运行。我正在尝试找到相对于col1的item_id的排名值。

I'm trying to use the RANKX formula rank the values of one column, but filtered for the value of a second column. In this example, col2 is a simple counter running in ascending value. I'm trying to find item_id's Rank value relative to the col1.

col1    col2
1001    8001
1001    8002
1002    8003
1002    8004
1002    8005

I想要找出一个col3，其内容为：

I'd like to figure out a col3 that would read:

col1    col2    col3
1001    8001    1
1001    8002    2
1002    8003    1
1002    8004    2
1002    8005    3

因为那将是col2相对于col1的排名。

Because that would be the rank of col2 relative to col1.

推荐答案

您根本不需要使用RANKX 。请参见精湛的 http://www.daxpatterns.com/ 中有关EARLIER的部分。在表中添加一个新的计算列：

You don't need to use RANKX at all. See the section on EARLIER in the superb http://www.daxpatterns.com/. Add a new calculated column in your table:

=
COUNTROWS (
    FILTER (
        MyTable,
        [col2] <= EARLIER ( [col2] )
            && [col1] = EARLIER ( [col1] )
    )
)

如果您确实想使用RANKX，则可以如下修改公式：

If you do want to use RANKX you can adapt the formula as follows:

=
RANKX (
    FILTER ( MyTable, [col1] >= EARLIER ( [col1] ) ),
    [Col2],
    ,
    1
)

这篇关于通过在DAX中的第二列上过滤来对一列的排名值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！