cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steve_Wheeler Established Member
Established Member

Re: ALL Function - Mystery?

It's good that your measure works as expected in a new PBIX file.  It's BAD that you had to be inspired to create a new PBIX file for it to work - something's broken there, especially since ALLEXCEPT still strangely worked.  I suggest you raise it as an issue with Microsoft.

 

The other issue you linked and the Microsoft response "The current Microsoft view is that this is by design behavior and customers should build their model with dimension tables if they need to use ALL functions in their calculations." surprises me:

  • I did a couple of Google searches on ALL FILTER WRONG IGNORE etc. and didn't turn any of this up.  Searching now on Power BI Support gave up the link you posted but only if you picked the right specific words;
  • The documentation of this behaviour seems to just be in that one community post.  the MSDN doco for the ALL function (https://msdn.microsoft.com/en-us/library/ee634802.aspx) should be updated to show that, two months after the issue was acknowledged; and
  • That the behaviour of using dimension tables to use ALL functions is by design seems to go against the idea of a BI service for non-experts, and seems to contradict what's happening for you, in your new PBIX at least.

At least we made some progress on it via this community, though not as simple as it could be.

Sean Super Contributor
Super Contributor

Re: ALL Function - Mystery?

Wow folks I figured it out! Smiley Happy

@Steve_WheelerMystery 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!!! Smiley Happy

 

Related to this post

http://community.powerbi.com/t5/Desktop/ALL-function-ignored-inconsistent-functionality-when-also-us...

@bswylie@OwenAuger

View solution in original post

Highlighted
Steve_Wheeler Established Member
Established Member

Re: ALL Function - Mystery?

@Sean,

Nice detective work.  That still seems like a bug to me.

OwenAuger Super Contributor
Super Contributor

Re: ALL Function - Mystery?

Yes this is an annoying problem with "sort by column" in Power BI (but not PowerPivot).

Discussed here in one of the DAX Puzzles
http://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/


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

Proud to be a Datanaut!




Steve_Wheeler Established Member
Established Member

Re: ALL Function - Mystery?

@OwenAuger,

Good point.  So not a bug, but a poorly documented feature/ side-effect/ use case...   Now that we know with hindsight what to look for, it's simple!  Also documented nicely here: https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-us...

 

 I wonder what we can do to avoid the next group also spending hours trying to figure out such an obscure issue?

josipinho Regular Visitor
Regular Visitor

Re: ALL Function - Mystery?

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 !

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 366 members 3,556 guests
Please welcome our newest community members: