Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bswylie
Helper I
Helper I

ALL() function ignored / inconsistent functionality when also using report level filter

While working on an analysis report we came across (what seems to be) an issue in Power BI, and are not sure if this is a bug or a misunderstanding on our part. In summary, a measure we have created which uses the ALL() filter modifier seems to calculate incorrectly when a report level filter is applied, and the correctness of the ALL function seems to vary depending on what is selected in the page filter.

 

We have been able to reproduce the strange behaviour using some very simple test data. The test data set we are using to portray this issue is shown in the image below:

 

00.png

 

When we implement the measure shown in the image below,

 

0.png

 

and have all the selection boxes of the DateID report filter selected,

 

1.png

 

the ALL() function works as expected, and ignores the corresponding slicers that we try to apply to the CountALLInstances measure that we have.  See images below.

 

 

Without start day or sick days filter applied (correct results)

 

2.png

 

Note that the CountFilteredInstances is similar but does not have the ALL function, and always performs as we expect.

With a start day filter applied (correct results)

 

3.png

With a sick day filter applied (correct results)

 

4.png

 

 

However, when we only have SOME of the DateID report filter options selected, the ALL() function is no longer able to turn off the corresponding slicers that we apply to the CountALLInstances.  The value for CountALLInstances that we would expect to see regardless of whether the Start Day or Sick Day slicer is applied is “6”, but this is not the case as can be seen in the images below

 

5.png

 

Without start day or sick days filter applied (correct results)

 

6.png

With a Start Day filter applied – incorrect results despite the ALL function. CountALLInstances should still be “6”.

 

7.png

With a Sick Days filter applied – incorrect results despite the ALL function. CountALLInstances should still be “6”.

 

8.png

We would greatly appreciate if anyone is able to identify whether this is a bug or is expected behaviour, and thus how we would work around this.

 

The desktop file is linked below for reference.

 

https://dl.dropboxusercontent.com/u/78036039/Sick%20Leave%20Report%20Filter%20Test%20v2%20-%20201610...

 

 

1 ACCEPTED SOLUTION
bswylie
Helper I
Helper I

Hi,

 

I got a response from Microsoft to my ticket and wanted to share the result. This did fix the problem as per screenshot below. I'm not sure why there is a difference between PBI and PowerPivot results, but I'm grateful to have something to work with. It is not clear whether the Power BI support team considers this a bug or not as they refer more to "problem" and "workaround". I was under the impression everything could be done from a flat table in PBI if only one source being used, but it's not much admin to add to link up at least. Thanks all for the input.

 

Microsoft support response

Use the ALL function on the dimension attribute instead of the column in the fact table, and use the slicer on the dimension attribute, this seems to workaround the problem.

 

Corrected.png

 

View solution in original post

22 REPLIES 22
Anonymous
Not applicable

Hi folks,

 

I am running into the same issue. Is there a solution to resolve this wihtout breaking out all columns that I potentially ever want to filter by into separate dimension tables? I mean there has to be a way to at least ditch all the filter context in a measure and then just reapply only the slicer that I want to keep, right?

 

Background:

I have a table with some 300 columns in my data model that users want to build reports for and there is absolutely zero chance I am breaking this out into dimension tables. IMHO is no proper version control or automation / programmation feature built into MS so I just won't do it. For the exat same reason, there are other voices that consider star schema not fit for many modern ways of working as explained in Why star schema is set up for failure and in Should auto exist and sort by column implementation be changed?

 

Cheers, aabtra

ttrandai
Frequent Visitor

Did Microsoft ever solve the bug? I am currently experiencing a similar problem, however I do not understand the solution provided in this post.

You need to use a star Schema.

So that the attribute you use in the All () function will function well.

 

For example - if you wish to see ALL Product sales, no matter what you choose in the slicer, you need to use a product table connected to the fact (sales) table. 

Then use the All() function on a column in the  Product table. this should work well. 

I found out that the ALL() function is ignored when it is applied to a column that is "sorted by" another column ... When I remove the "Sort By Column", it's working again... 

Sean
Community Champion
Community Champion

Hello @bswylie !

 

The SQLBI guys just wrote an article about this.

If you'd like add this link to your post you marked as solution.

Since the solution shows up on the first page immediately after the question other people can see it right away.

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

 

The golden rule of data modeling is always the same: always use star schemas.

If a column has to be used to slice and dice, then it needs to belong to a dimension.

Numbers to aggregate, on the other hand, are stored in fact tables.

Tabular lets a developer deviate from the regular star schema architecture.

This does not mean that doing it is always a good idea. It seldom is.”

Alberto Ferrari

 

The SQLBI guys wrote the above article in response to a recent question similar to yours:

https://community.powerbi.com/t5/Desktop/Suspected-BUG-when-using-ALL-with-multiple-non-ignored-slic...

 

Thanks! Smiley Happy

 

bswylie
Helper I
Helper I

Hi,

 

I got a response from Microsoft to my ticket and wanted to share the result. This did fix the problem as per screenshot below. I'm not sure why there is a difference between PBI and PowerPivot results, but I'm grateful to have something to work with. It is not clear whether the Power BI support team considers this a bug or not as they refer more to "problem" and "workaround". I was under the impression everything could be done from a flat table in PBI if only one source being used, but it's not much admin to add to link up at least. Thanks all for the input.

 

Microsoft support response

Use the ALL function on the dimension attribute instead of the column in the fact table, and use the slicer on the dimension attribute, this seems to workaround the problem.

 

Corrected.png

 

Thanks for the update @bswylie

 

I don't think this is an adequate solution - I will send some feedback myself 🙂

 

There could be man valid scenarios where you want to clear a filter on one column and retain filters on another column from the same table (e.g. two columns in the same lookup table).

 

This issue seems to be in a similar vein to this one (but a different problem):

https://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/solution/

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

I have seen that the problem is generated by the DAX code below generated by Power BI.

The issue is the behavior of SUMMARIZECOLUMNS, but I have to say that I never thought to use SUMMARIZECOLUMNS using two columns from the same table in two table filter arguments. If this is what Power BI does, then SUMMARIZECOLUMNS should work accordingly.

The reason why Excel works is because Excel generates queries in MDX using a completely different approach.

However, I woulds say that this is definitely an issue in the DAX engine.

 

DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Sheet1'[Date ID])),
      OR(OR('Sheet1'[Date ID] = "01", 'Sheet1'[Date ID] = "02"), 'Sheet1'[Date ID] = "03")
    )

  VAR __DS0FilterTable2 = 
    FILTER(KEEPFILTERS(VALUES('Sheet1'[Start Day])), 'Sheet1'[Start Day] = "Tue")

EVALUATE
  SUMMARIZECOLUMNS(
    __DS0FilterTable,
    __DS0FilterTable2,
    "CountALLInstances", IGNORE('Sheet1'[CountALLInstances])
  )

@marcorusso - Thanks for your feedback. I spent lots of hours on this issue. cant beleive that same formula acts differently in StarSchema and Flat table.

Got this reply from Microsoft PBI Support...

 

Hello Brian,

 

Apologies in taking a little time getting back to you.

 

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.

 

However, regarding the point from your mail “My understanding was that this was some of the power BI principle – could consume and filter from a single flat table?”.  We do agree that Power BI should support the scenario of single fact table model.  We are trying to get a view from the Chief Architect from this area who is unfortunately on vacation at the moment.

 

Many thanks for your patience.

I added a dimension table just to try out this solution, and it did not work. I got the same behavior as when the ALL function was used on the fact table. The ALL function should support a single Fact table model anyway, but even the work-around does not work.

@marcorusso thanks very much for taking a look! I did ask the support team who responded to me whether this was classified a bug on their side and whether there are to be fixes in the future for this - they are checking with the Power BI "Product Group" - I'll post whatever reply I get here!

 

 

bswylie
Helper I
Helper I

Am waiting on Microsoft Power BI team to respond... For interest - I built the same test in PowerPivot and found that it worked perfectly. Screenshot below

 

Which is a little concerning for Power BI. It was pretty obvious to me that something didn't seem right, but what if there are other DAX engine issues which seem more subtle? I'm sure Microsoft will help sort it out quickly though.

 

PowerPivot Working.png

@bswylie

 

Thanks for pointing out this issue and raising with Microsoft!

 

I played around with your example and created a simple example of my own, and the behaviour seems to be the same whether the filters are applied through slicers or Report Level filters.

 

The behaviour seems to be:

  1. For the set of columns that have 'slicers' (or I guess any filter) applied (i.e. the Columns for which ISFILTERED( Column ) = TRUE ), cross-filtering is applied among those columns.
  2. This results in a reduced list of values per filtered column.
  3. When the DAX expression is evaluated, ALL(...) correctly clears the filters on columns specified, but the cross-filtered values for the other columns are retained!!!

 

For example, when you filtered Date ID = 01, 02, 03 and StartDay = Mon, cross-filtering reduced Date ID down to just 01.

Then the DAX expression cleared the filter on StartDay, but Date ID was left filtered to just 01, resulting in a row count of 3.

 

Of course this is all incorrect behaviour. Hopefully fixed soon!

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger - agreed, also found that slicers exhibit same problem. I just used the use case where I had it on the filters pane - so I think fundamentally to do with how the engine is handling "filtering" before applying the DAX formula

Thanks Owen and Sean for also looking into this, much appreciated! I did raise a ticket with Microsoft through the ticket system on the website on Monday afternoon SA Time ( Monday morning US time). As a Pro subscriber, they said I'd hear back from them in one business day... I've heard nothing back from them. Anybody know how I can raise the urgency with them on this? As an "engine" issue, this is causing patently incorrect results on my report, and I'm sure may other people's reports too, but perhaps they are just not noticing it because it is less obvious! So I perceive this to be fairly serious and thought I'd have heard back from them by now.

 

For anybody Microsoft on this forum, my ticket number is 116101014778110.

 

As an aside, Icouldn't find any send a frown on the desktop version or online version, and the "smile" button just gives you an option to "get help" which takes you to https://powerbi.microsoft.com/en-us/support/ which is where I logged the ticket...

Sean
Community Champion
Community Champion

Yep! Curious to see what they say about this?

 

2016-10-12 - ALL Function with Slicers.png

@Sean - will let you know! Do you know of any other way I can contact Microsoft about this, as have had no reply to my support ticket?

Sean
Community Champion
Community Champion

We seem to be getting different results using the same exact Measure in Power Pivot and in PBI !

 

Outside of Microsoft only @marcorusso can tell us why this may be happening... Smiley Happy

bswylie
Helper I
Helper I

Hi all,

 

Can anyone tell me if this does seem to be a bug or if we are doing something wrong? Would greatly appreciate if anyone can respond and say whether they also find this strange behaviour?

 

Cheers

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.