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

 

SymbolRepCodeHousehold CountKeyDynamic Household CountHousehold Frequency
AAPLAA2C1AAPLAA2C1110 or More Households
AAPLAA091AAPLAA091110 or More Households
AAPLAA162AAPLAA161110 or More Households
AAPLAA3F7AAPLAA3F1110 or More Households
ABBAA3F1ABBAA3F63 to 9 Households
ABBAA163ABBAA1663 to 9 Households
ABBAA2C2ABBAA2C63 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. 

 

SymbolRepCodeHousehold CountKeyDynamic Household CountHousehold Frequency
AAPLAA2C1AAPLAA2C11 to 2 Households
AAPLAA091AAPLAA0911 to 2 Households
AAPLAA162AAPLAA1621 to 2 Households
AAPLAA3F7AAPLAA3F73 to 9 Households
ABBAA3F1ABBAA3F11 to 2 Households
ABBAA163ABBAA1633 to 9 Households
ABBAA2C2ABBAA2C21 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
Did I answer your question? Mark my post as a solution!

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 ]

Img.png

 

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
Did I answer your question? Mark my post as a solution!

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

 

Review my previous answer.




Lima - Peru
Did I answer your question? Mark my post as a solution!

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!