Forum Discussion
ALL Function - Mystery?
Sean,
Maybe it's related to some obscure cross-filtering issue from Payments to Asset to Location.
What do you get if you try:
County Total =
CALCULATE ( [Total Net], ALLEXCEPT ( Payments, Location[County] ) )Wow folks I figured it out! :smileyhappy:
AnonymousMystery solved!
The culprit was a Conditional Column created in the Query Editor
That Column was called Category Sort and I basically used it so in charts the Categories show in order of importance and not A-Z
If anyone wants to see what actually happens just follow these steps:
1) Load all 3 sample tables I posted on Page 1
2) Write these 2 Measures
Total Net = SUM ( Payments[Net] ) County Total = CALCULATE ( [Total Net], ALL ( Payments[Subcategory], Payments[Category] ) )
3) Create a Matrix with County, Category and Subcategory in the Rows and then add the 2 Measures to the Values
Everything works great!
4) Now click Edit Queries and Add a Conditional Column in the Payments Table =>Category Sort
you can use the UI and basically
if Category is Cat 2 then 1, else if Category is Cat 3 then 2, otherwise 1 => OK => Close and Apply
so far so good nothing is affected yet!
5) Now go to the Data View => Payments table
=> select the Category Column => click Sort By Column => select the Category Sort column
6) Now go back and look at the Matrix => the County Total Measure no longer works as intended
HAPPY NEW YEAR!!! :smileyhappy:
Related to this post
Wow folks I figured it out! :smileyhappy:
AnonymousMystery solved!
The culprit was a Conditional Column created in the Query Editor
That Column was called Category Sort and I basically used it so in charts the Categories show in order of importance and not A-Z
If anyone wants to see what actually happens just follow these steps:
1) Load all 3 sample tables I posted on Page 1
2) Write these 2 Measures
Total Net = SUM ( Payments[Net] ) County Total = CALCULATE ( [Total Net], ALL ( Payments[Subcategory], Payments[Category] ) )
3) Create a Matrix with County, Category and Subcategory in the Rows and then add the 2 Measures to the Values
Everything works great!
4) Now click Edit Queries and Add a Conditional Column in the Payments Table =>Category Sort
you can use the UI and basically
if Category is Cat 2 then 1, else if Category is Cat 3 then 2, otherwise 1 => OK => Close and Apply
so far so good nothing is affected yet!
5) Now go to the Data View => Payments table
=> select the Category Column => click Sort By Column => select the Category Sort column
6) Now go back and look at the Matrix => the County Total Measure no longer works as intended
HAPPY NEW YEAR!!! :smileyhappy:
Related to this post
I spent 2 days trying to figure this one out. Completely even forgot about setting that sort with a conditional calculated column. Cant belive something so silly would cause such trouble.
Thank you for the solution !