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.
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!
