Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Filter Key to keep only the top 2 rows. Does the result change?
@Vvelarde Sorry, missed the bottom portion of your post. Thank you for confirming it's not possible. Best regards!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |