Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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))", h...
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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