SELECTCOLUMNS vs ALL(Column)

[Anonymous]

Can someone help me understand why these two measure give different results? I would expect that "ALL(Table[Category])" to be equivelent to "SELECTCOLUMNS(ALL(Table), "Category", Table(Category))", however...

Category Rank = 
	RANKX(
		ALL('Table'[Category]) ,
		CALCULATE(
			SUM('Table'[My Value])
			)
	)

Category Rank 2 = RANKX(
                SELECTCOLUMNS(
                    ALL('Table'),
                    "Category",
                    'Table'[Category]),
                CALCULATE(SUM('Table'[My Value]))
            )

Yeilds the following results:

I expect the same results. I included the count because I noticed the Rank 2's rankings are seperated by the count of records between the two which may provide a clue.

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Look at my test

RANK = RANKX(ALL(Sheet4[Category]),CALCULATE(SUM(Sheet4[My Value])))

RANK2 = RANKX(SELECTCOLUMNS(ALL(Sheet4),"category",Sheet4[Category]),CALCULATE(SUM(Sheet4[My Value])))

above two measures are just like yours.

In the first measure, ALL(Sheet4[Category]) means to ignore filters in the Sheet4[Category] column, but don't ignore filters on other columns of Sheet4. So the "RANK" should ignore filters in the Sheet4[Category], only consider the "My Value" column.

In the second measure,SELECTCOLUMNS(ALL(Sheet4),"category",Sheet4[Category]) just returns a table which has a column "category". it doesn't ignore filters on any column. Thus,"RANK2" should consider "Category" and "My Value" column.

By a little modification to the "RANK2", it can get the same result as "RANK".

RANK5 = RANKX(SELECTCOLUMNS(ALL(Sheet4),"category",Sheet4[Category]),CALCULATE(SUM(Sheet4[My Value])),,DESC,Dense)

Best Regards

Maggie