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
You are right, you need total by county regardless what category/sub category it is. Not sure why this will work on sample data and not on real data.
I was referring to your "County Total" which missing SUM function, I'm sure that was typo.
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] ) )
Good point Anonymous
AnonymousThank You! That did it and I get why/how it works! :smileyhappy:
But I'm still wondering however why my original Measure works with the sample data I created but not with my real data
The relationships are set up the same way as in the sample - go figure...
Anyway Thanks again!
That's good news, though the new version of the measure will likely cause problems if you want to also filter on Date, Company etc. - you'll need to add any slicer columns into the ALLEXCEPT to have the measure respect them I think, if that is required.
I can't see what's wrong with your original measure either, especially as you have single direction cross filters in place - there shouldn't be any weird filtering up to Asset and back down to Payment.
This shouldn't be hard!
I wonder if you get a different result in your original measure if you change the order of the columns in the ALL:
County Total = CALCULATE ( [Total Net], ALL(Payments[Category], Payments[Subcategory]) )
Or do you have any unexpected cross-pollination of Category and Subcategory - e.g. Sub 2.1 in Cat3 and Cat2?
Anonymous
I'm just even more confused today...
I remembered this post here
1) So I loaded the tables in PowerPivot - and guess what my Measure produces the expected result with the real data!
2) Then I decided to try in a brand new pbix file - same thing - my Measure produces the expected result again with the real data!
I mean this is not a complicated measure! So it seems there's nothing wrong with my Data either.
I really really don't want to have to recreate this pbix file from scratch...
I've tried deleting all relationships and setting them up again - nothing seems to help fix the original pbix file
I've tried deleting all other tables in the data model except those 3 - no success
I've tried reversing the order of the columns in the Measure didn't make a difference BTW Thanks for the suggestion!
I've tried each column in its own ALL function - no change
And the weird thing is in PowerPivot and in a new pbix the Measure works as expected!
One final thought my Measure would not require the adjustments you mention regarding the Slicers!
Anyway if I figure it out I'll post back here!
But sadly it seems I'll have to recreate this file :smileysad: