Forum Discussion
DAX - Can a Calculated Column Ignore Row Context and Keep Table Filter Context?
Is it possible for a DAX Calculated Column to keep table filter context but ignore row filter context? I have tried every combination of CALCULATE, EARLIER, and ALL, ALLEXCEPT, ALLSELECTED to no avail.
Ideal State - The calculated column Dynamic Household Count recognizes the TABLE FILTER context coming in through the relationship of the KEY column and recalculates the Dynamic Household Count based on the sum of Household Count for all of the same Symbol. I realize this can be done using a measure BUT the Househould Frequency column is used to drive additional analysis across facts/dimensions in the model. Counting Househould Frequency is a major requirement and can only be done as a column.
| Symbol | RepCode | Household Count | Key | Dynamic Household Count | Household Frequency |
| AAPL | AA2C | 1 | AAPLAA2C | 11 | 10 or More Households |
| AAPL | AA09 | 1 | AAPLAA09 | 11 | 10 or More Households |
| AAPL | AA16 | 2 | AAPLAA16 | 11 | 10 or More Households |
| AAPL | AA3F | 7 | AAPLAA3F | 11 | 10 or More Households |
| ABB | AA3F | 1 | ABBAA3F | 6 | 3 to 9 Households |
| ABB | AA16 | 3 | ABBAA16 | 6 | 3 to 9 Households |
| ABB | AA2C | 2 | ABBAA2C | 6 | 3 to 9 Households |
Current State - =CALCULATE(SUMX('Table','Table'[Household Count]),FILTER(ALLEXCEPT('Table','Table'[Key]),EARLIER('Table'[Symbol]) = 'Table'[Symbol])) doesn't work. It keeps the Row Filter context. You can change the ALLEXCEPT to ALL and then it IGNORES both row context and table filter context giving the SUM for each Symbol using a full table scan.
| Symbol | RepCode | Household Count | Key | Dynamic Household Count | Household Frequency |
| AAPL | AA2C | 1 | AAPLAA2C | 1 | 1 to 2 Households |
| AAPL | AA09 | 1 | AAPLAA09 | 1 | 1 to 2 Households |
| AAPL | AA16 | 2 | AAPLAA16 | 2 | 1 to 2 Households |
| AAPL | AA3F | 7 | AAPLAA3F | 7 | 3 to 9 Households |
| ABB | AA3F | 1 | ABBAA3F | 1 | 1 to 2 Households |
| ABB | AA16 | 3 | ABBAA16 | 3 | 3 to 9 Households |
| ABB | AA2C | 2 | ABBAA2C | 2 | 1 to 2 Households |
6 Replies
Hi, try with:
Household Count M = CALCULATE ( SUM ( Table1[Household Count] ), FILTER ( ALL ( Table1 ), Table1[Symbol] = EARLIER ( Table1[Symbol] ) ) )Regards
Victor
When using ALL, it ignores both ROW CONTEXT and TABLE FILTER CONTEXT, so this will not achieve what I am trying to do. It computes the SUM for all rows in the table, not the current rows based on the TABLE FILTER CONTEXT.
This is not what you expected?
Is you want to this column will be dynamic, that's not possible. The calculated columns are "calculated" when the model is loading. Not change with filters or slicers in the report visual.
Regards
Victor
