DAX - Can a Calculated Column Ignore Row Context and Keep Table Filter Context?

[MKEHawkeye]

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

[Vvelarde]

@MKEHawkeye

Hi, try with:

Household Count M =
CALCULATE (
    SUM ( Table1[Household Count] ),
    FILTER ( ALL ( Table1 ), Table1[Symbol] = EARLIER ( Table1[Symbol] ) )
)

Regards

Victor

[MKEHawkeye]

@Vvelarde

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.

[Vvelarde]

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

[MKEHawkeye]

Filter Key to keep only the top 2 rows. Does the result change?

[Vvelarde]

@MKEHawkeye

Review my previous answer.

[MKEHawkeye]

@Vvelarde Sorry, missed the bottom portion of your post. Thank you for confirming it's not possible. Best regards!