## Desktop

Frequent Visitor
Posts: 5
Registered: ‎06-20-2018

# 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
Super User
Posts: 1,666
Registered: ‎05-10-2016

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

@MKEHawkeye

Hi, try with:

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

Regards

Victor

Lima - Peru

Proud to be a Datanaut!

Frequent Visitor
Posts: 5
Registered: ‎06-20-2018

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

[ Edited ]

@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.

Super User
Posts: 1,666
Registered: ‎05-10-2016

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

[ Edited ]

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

Lima - Peru

Proud to be a Datanaut!

Frequent Visitor
Posts: 5
Registered: ‎06-20-2018

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

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

Super User
Posts: 1,666
Registered: ‎05-10-2016

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

@MKEHawkeye

Lima - Peru

Proud to be a Datanaut!

Highlighted
Frequent Visitor
Posts: 5
Registered: ‎06-20-2018

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

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