Search code examples
daxpowerpivot

Powerpivot Rankx with multiple filters

I want to assign a row number for [Site] after it's filtered based on three simple criteria: whether they have the same [Year], [Identifier], and where the [LeftOrJoined] column is "JOINED".

So I want the [Rank] column below:

[Year] | [Identifier] | [LeftOrJoined] | [Site] | [Rank]
2012      1             LEFT             A         
2012      1             JOINED           B         1
2012      1             JOINED           C         2
2013      2             LEFT             A
2013      2             JOINED           B         1
2013      2             JOINED           C         2

The formula I have is:

Rankx(
    filter(table,
        allexcept(table,
                [LeftOrJoined]="JOINED",[Year]=[Year],
                [Identifier]=[Identifier])),
                    [Site], ,1,dense)

But I get the error: The ALLEXCEPT function expects a table reference expression for argument '2', but a string or numeric expression was used.

I feel I'm making a basic mistake. Any help greatly appreciated!

Solution

  • I'm not sure exactly how your formula is supposed to work, but the following works for me:

    Rank = IF(Table[LeftOrJoined] <> "JOINED", BLANK(),
          RANKX(FILTER(Table, Table[LeftOrJoined] = "JOINED"),
                Table[Site], , 1, Dense))

    If LeftOrJoined is not "JOINED", then return a blank, otherwise, rank the Site on the rows where LeftOrJoined is equal to "JOINED".