Forum Discussion

rlansing's avatar
rlansing
Icon for Resolver I rankResolver I
10 years ago
Solved

ALLEXCEPT only working on visible row context

Hello, I have a quick question. I am trying to calculate the $ Change (YOY) for a sub category, and then trying to compare that overall sub category change to specific products. Basically, we want t...
  • rlansing's avatar
    rlansing
    10 years ago

    Have you ever posted a question and then answered it immediately afterwards?

     

    Here is the solution:

    CALCULATE( [$ Vol Ch], ALLEXCEPT ( Data, Data[Date], Data[Market], Data[Sub-Cat] ), VALUES( Data[Sub-Cat] ) )
rlansing's avatar
rlansing
Icon for Resolver I rankResolver I
10 years ago

Have you ever posted a question and then answered it immediately afterwards?

 

Here is the solution:

CALCULATE( [$ Vol Ch], ALLEXCEPT ( Data, Data[Date], Data[Market], Data[Sub-Cat] ), VALUES( Data[Sub-Cat] ) )
greggyb's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

Note, there is no row context in a table visual or a matrix visual. Row context only exists in a physical data model table (imported or calculated), and in a small subset of functions, the most common of which are FILTER(), *X() functions, and ADDCOLUMNS().

 

The reason that your original construction doesn't work is that there is no filter context on [Sub-Cat] in the matrix that doesn't include this field as a label.

 

VALUES() is evaluated in filter context, so your VALUES( 'Data'[Sub-Cat] ) is evaluated in the filter context of a given [Product Desc]. The result of this VALUES() is combined with a logical and with your ALLEXCEPT(). You should be able to remove 'Data'[Sub-Cat] from the ALLEXCEPT() and get the same result.