Forum Discussion
ALLEXCEPT only working on visible row context
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] ) )
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] ) )
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.
That makes a lot of sense. Thank you for your help!
A really good test to determine if you're in row context or not is to make a bare column reference - e.g. 'Table'[Field] not contained in any function. If the expression evaluates without the "Value for column cannot be determined in the current context..." error, then you're in a row context. If you do get that error on a bare column reference, then you're in a filter context.
E.g.
// DAX // Calculated Column NewColumn = 'Table'[OtherColumn] // The above works - row context means there's only one value for that // field // Measure NewMeasure = 'Table'[NewColumn] // The above will fail, because a filter context cannot syntactically // guarantee a single value for that field. See below. //Measure CalculateNewMeasure = CALCULATE( 'Table'[NewColumn] ,'Table'[PrimaryKey] = 1 ) // The above will fail, because CALCULATE() creates a filter context. // Even though we know logically that the [PrimaryKey] field will only // identify a single row of 'Table', this is not something that can be // inferred solely from the syntax of the statement.The difference is what we, as model authors and intelligent human beings, can infer from the content of an expression, vs what the DAX interpreter can logically deduce only from the form of the expression.
A filter context can be created that we know to have a single row for a given field. Given that filter context, it is possible to alter the underlying data such that there will be multiple rows in the table that meet that filter context.
You might object and say that the following should work:
// DAX // Measure MeasureWithSingleValue = CALCULATE( 'Table'[Field] ,'Table[Field] = 1 )In this case, our expression to evaluate and our filter are both referencing the same field. There is still no aggregation around 'Table'[Field], and there might be multiple rows that have [Field] = 1. Even if the field is our primary key, there is no syntactic way to derive that in DAX.
The below variation will return a value, either blank or 1, depending on the existence of a 1 in [Field]:
// DAX // Measure MeasureWithSingleValue2 = CALCULATE( VALUES( 'Table'[Field] ) ,'Table'[Field] = 1 )What's the difference? VALUES() returns distinct values from a field. Now, even if there are many rows with [Field] = 1, we've logically grouped those to a single value with VALUES().