Forum Discussion
SELECTCOLUMNS vs ALL(Column)
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.
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
1 Reply
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