Forum Discussion
ALL() function ignored / inconsistent functionality when also using report level filter
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.
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/
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 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!
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 for your feedback. I spent lots of hours on this issue. cant beleive that same formula acts differently in StarSchema and Flat table.
