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 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:
- 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.
- This results in a reduced list of values per filtered column.
- 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!
Yep! Curious to see what they say about this?
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?
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... :smileyhappy: